﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.DTO
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports Camstar.WebClient.WebServicesProxy.InSiteWebServices
Imports DatabaseAccess.DBUtility
Imports System.Configuration

Public Class uMES_bjDispatchBusiness
    Implements uMES_bjDispatchIBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

#Region "修改报工零件编号信息"
    Function UpdateReportProductNoList(ByVal strReportID As String, ByVal dtChildContainer As DataTable) As uMESProcessResult Implements uMES_bjDispatchIBusiness.UpdateReportProductNoList
        Dim r As uMESProcessResult = dal.UpdateReportProductNoList(strReportID, dtChildContainer)

        Return r
    End Function
#End Region

#Region "获取产品合格证列表"
    Function GetQualificationList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetQualificationList
        Dim strQuery As String = "SELECT * FROM qualification q" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf

        If para.Keys.Contains("FactoryEName") = True Then
            strQuery += "AND q.mfgfactory = '" & para("FactoryEName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("FullName") = True Then
            strQuery += "AND q.checkemployee = '" & para("FullName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND LOWER(q.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("MfgTaskNo") = True Then
            strQuery += "AND LOWER(q.inventtaskname) LIKE '%" & para("MfgTaskNo").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(q.containername) LIKE '%" & para("ContainerName").ToString.ToLower & "%'" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "查询批次信息主SQL"
    Function GetContainerInfoSQL() As String
        Dim strQuery As String = "SELECT c.containername,pb.productname,p.description,p.productrevision,p.productid,c.originalqty,c.qty,c.containercomments," & vbCrLf & _
                                "   wfb.workflowname,wf.workflowrevision,wf.workflowid,u.uomname,wc.workcentername,c.isselfmfg," & vbCrLf & _
                                "   sb.specname,s.specrevision,c.plannedstartdate,c.plannedcompletiondate,f.factoryname,c.materialrequirementdate," & vbCrLf & _
                                "   mo.mfgordername,o.ownername,pc.prioritycodename,wfs.workflowstepname,c.containerid,pf.productfamilyname," & vbCrLf & _
                                "   mo.mfgbatch,pm.phasemarkname,iti.inventtaskinfoname,c.containertype,c.togetherflag,pt.producttypename," & vbCrLf & _
                                "   (CASE WHEN" & vbCrLf & _
                                "       (SELECT hml.containerid" & vbCrLf & _
                                "       FROM historymainline hml" & vbCrLf & _
                                "       LEFT JOIN cdodefinition cd ON cd.cdodefid = hml.txntype" & vbCrLf & _
                                "       LEFT JOIN speclastcheckhistory slc ON slc.historymainlineid = hml.historymainlineid" & vbCrLf & _
                                "       WHERE cd.cdoname = 'SpecLastCheck' AND slc.checktype = 2 AND hml.reversalstatus = 1" & vbCrLf & _
                                "       AND ROWNUM = 1 AND (hml.containerid = c.containerid OR hml.containerid = c.parentcontainerid)) IS NULL THEN '0' ELSE '1' END" & vbCrLf & _
                                "   ) AS iszhongjian," & vbCrLf & _
                                "(CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN currentstatus cs ON cs.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "LEFT JOIN producttype pt ON pt.producttypeid = p.producttypeid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = cs.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cs.factoryid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cs.workflowstepid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid" & vbCrLf & _
                                "LEFT JOIN uom u ON u.uomid = c.uomid" & vbCrLf & _
                                "LEFT JOIN workcenter wc ON wc.workcenterid = c.workcenterid" & vbCrLf & _
                                "LEFT JOIN owner o ON o.ownerid = c.ownerid" & vbCrLf & _
                                "LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "LEFT JOIN inventtaskinfo iti ON iti.inventtaskinfoid = mo.inventtaskinfoid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf

        Return strQuery
    End Function
#End Region

#Region "获取与给定批次号的TogetherFlag相同的批次列表"
    Function GetContainerForTogetherFlag(ByVal strContainerName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetContainerForTogetherFlag
        ''单件单卡同批次标记
        Dim strTogetherFlag As String = ""
        Dim r As uMESProcessResult = GetContainerInfo(strContainerName)
        If r.Result = True Then
            Try
                strTogetherFlag = r.DBTable.Rows(0)("TogetherFlag").ToString
            Catch ex As Exception
                strTogetherFlag = ""
            End Try
        End If

        Dim strQuery As String = GetContainerInfoSQL()

        If strTogetherFlag <> "" Then
            strQuery += "AND c.togetherflag = '" & strTogetherFlag & "'" & vbCrLf
        Else
            strQuery += "AND c.containername = '" & strContainerName & "'" & vbCrLf
        End If

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段派工跟踪卡列表"
    Public Function GetWorkCenterDispatchContainerList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetWorkCenterDispatchContainerList
        ''单件单卡同批次标记
        Dim strTogetherFlag As String = ""
        If para.Keys.Contains("ContainerName") = True Then
            Dim strContainerName As String = para("ContainerName").ToString
            Dim r As uMESProcessResult = GetContainerInfo(strContainerName)
            If r.Result = True Then
                Try
                    strTogetherFlag = r.DBTable.Rows(0)("TogetherFlag").ToString
                Catch ex As Exception
                    strTogetherFlag = ""
                End Try
            End If
        End If

        Dim strQuery As String = "SELECT mt.*," & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT fullname" & vbCrLf & _
                                "       FROM" & vbCrLf & _
                                "       (" & vbCrLf & _
                                "           SELECT e.employeeid,e.employeename,e.fullname,wcd.containerid,wcd.specid" & vbCrLf & _
                                "           FROM workcenterdispatching wcd" & vbCrLf & _
                                "           LEFT JOIN employee e ON e.employeeid = wcd.mainmfgemployeeid" & vbCrLf & _
                                "           WHERE wcd.dispatchtotype = 1" & vbCrLf & _
                                "           ORDER BY wcd.dispatchdate ASC" & vbCrLf & _
                                "       ) wcd2" & vbCrLf & _
                                "       WHERE ROWNUM = 1 AND wcd2.containerid = mt.containerid AND wcd2.specid = mt.nextSpecid" & vbCrLf & _
                                "   ) AS nextMainMfgEmployee" & vbCrLf & _
                                "FROM" & vbCrLf & _
                                "(" & vbCrLf & _
                                "  SELECT mo.mfgordername,c.containername,pb.productname,p.description,p.productrevision,c.originalqty,c.qty,pc.prioritycodename,sb.specname," & vbCrLf & _
                                "   s.specrevision,c.plannedstartdate,c.plannedcompletiondate,wfs.workflowid,c.containerid,pf.productfamilyname,pm.phasemarkname,wc.workcentername," & vbCrLf & _
                                "   mo.notes AS TechniqueNotes," & vbCrLf & _
                                "   (CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold," & vbCrLf & _
                                "   (CASE WHEN s.iskey = 1 THEN 'G' ELSE '' END) AS iskey," & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT wfs2.specid FROM" & vbCrLf & _
                                "       (" & vbCrLf & _
                                "           SELECT wfs1.workflowid,wfs1.sequence," & vbCrLf & _
                                "               (CASE WHEN wfs1.specid = '0000000000000000' THEN (SELECT sb1.revofrcdid FROM specbase sb1 WHERE sb1.specbaseid = wfs1.specbaseid) ELSE wfs1.specid END) AS specid" & vbCrLf & _
                                "           FROM workflowstep wfs1 ORDER BY wfs1.sequence ASC" & vbCrLf & _
                                "       ) wfs2" & vbCrLf & _
                                "       WHERE ROWNUM = 1 AND wfs2.sequence > wfs.sequence AND wfs2.workflowid = wfs.workflowid" & vbCrLf & _
                                "   ) AS nextSpecid" & vbCrLf & _
                                "   FROM container c" & vbCrLf & _
                                "   LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "   LEFT JOIN WorkCenter wc ON wc.workcenterid = c.workcenterid" & vbCrLf & _
                                "   LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "   LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "   LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "   LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "   LEFT JOIN PriorityCode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "   LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "   LEFT JOIN factory f on f.factoryid = cu.factoryid" & vbCrLf & _
                                "   LEFT JOIN spec s ON s.specid = cu.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "   LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "   LEFT JOIN workflow w ON w.workflowid = wfs.workflowid" & vbCrLf & _
                                "   LEFT JOIN cdodefinition cdo ON cdo.cdodefid = w.cdotypeid" & vbCrLf & _
                                "   WHERE 1 = 1 AND cdo.cdoname = 'MachineMfgWorkFlow'" & vbCrLf & _
                                "   AND c.status = 1 AND c.containername <> c.containerid AND c.parentcontainerid IS NULL" & vbCrLf & _
                                "   AND c.containerid NOT IN" & vbCrLf & _
                                "      (" & vbCrLf & _
                                "          SELECT hml.containerid" & vbCrLf & _
                                "          FROM historymainline hml" & vbCrLf & _
                                "          LEFT JOIN cdodefinition cd ON cd.cdodefid = hml.txntype" & vbCrLf & _
                                "          LEFT JOIN speclastcheckhistory slc ON slc.historymainlineid = hml.historymainlineid" & vbCrLf & _
                                "          WHERE cd.cdoname = 'SpecLastCheck' AND slc.checktype = 2 AND hml.reversalstatus = 1" & vbCrLf & _
                                "      )" & vbCrLf

        If para.Keys.Contains("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("WorkCenterName") = True Then
            strQuery += "AND wc.workcentername = '" & para("WorkCenterName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("MfgOrderName") = True Then
            strQuery += "AND LOWER(mo.mfgordername) LIKE '%" & para("MfgOrderName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            If strTogetherFlag = "" Then
                strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
            Else
                strQuery += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
            End If
        End If
        'If para.Keys.Contains("SecretLevel") = True Then
        '    strQuery += "AND (c.secretlevel <= " & para("SecretLevel").ToString & " OR c.secretlevel IS NULL)" & vbCrLf
        'End If
        If para.Keys.Contains("SearchContainerName") = True Then
            strQuery += "AND LOWER(c.containername) LIKE '%" & para("SearchContainerName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("PriorityCode") = True Then
            strQuery += "AND LOWER(pc.prioritycodename) = '" & para("PriorityCode").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND c.plannedcompletiondate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND c.plannedcompletiondate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        strQuery += ") mt" & vbCrLf & _
                    "ORDER BY mt.containername ASC" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            Dim DT As DataTable = r.DBTable.Copy
            DT.Columns.Add("NextMainMfg")
            For i As Integer = 0 To DT.Rows.Count - 1
                If Not IsDBNull(DT.Rows(i)("nextMainMfgEmployee")) Then
                    Dim strHypeLink As String = ""
                    strHypeLink = "<a href = '#' onclick = ""ViewDispatchAndReport('" & DT.Rows(i)("ContainerID").ToString & "','" & DT.Rows(i)("NextSpecID").ToString & "')"" style='font-size:12px; color:blue;'>" & DT.Rows(i)("nextMainMfgEmployee").ToString & "</a>"
                    DT.Rows(i)("NextMainMfg") = strHypeLink
                Else
                    DT.Rows(i)("NextMainMfg") = ""
                End If
            Next

            result.Result = True
            result.DBTable = DT
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取生产问题列表"
    Function GetMfgProblemList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetMfgProblemList
        Dim strQuery As String = "SELECT up.ufcproblemname,ce.employeename,ce.fullname,up.ufccreatedate,f.factoryname,upt.ufcproblemtypename" & vbCrLf & _
                                "   ,r.resourcename,toe.employeename AS toemployeename,toe.fullname AS tofullname,up.ufcwtdj" & vbCrLf & _
                                "   ,pb.productname,p.productrevision,p.description,c.containername,c.qty,up.ufcljxh,up.ufccontent,up.ufcResult" & vbCrLf & _
                                "   ,CASE WHEN up.ufcstatus=0 THEN '已关闭' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '已处理' END AS ufcstatus" & vbCrLf & _
                                "   ,CASE WHEN LOWER(ce.employeename)='' AND up.ufcresult IS NULL THEN 1 ELSE 0 END AS tjrflag" & vbCrLf & _
                                "FROM ufcproblem up" & vbCrLf & _
                                "LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = up.resourceid" & vbCrLf & _
                                "LEFT JOIN employee toe ON toe.employeeid = up.ufctoemployeeid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = up.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = up.containerid" & vbCrLf & _
                                "LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid = up.ufcproblemtypeid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = up.factoryid" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf

        If para.Keys.Contains("FactoryName") = True Then
            strQuery += "AND (f.factoryname = '" & para("FactoryName").ToString & "' OR up.factoryid IS NULL)" & vbCrLf
        End If
        If para.Keys.Contains("CreateEmployee") = True Then
            strQuery += "AND LOWER(ce.fullname) LIKE '%" & para("CreateEmployee").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ToEmployee") = True Then
            strQuery += "AND LOWER(toe.fullname) LIKE '%" & para("ToEmployee").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND up.ufccreatedate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND up.ufccreatedate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工序派工信息"
    Public Function GetSpecDispatchInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetSpecDispatchInfo
        Dim strWorkflowID As String = para("WorkflowID").ToString
        Dim strContainerID As String = para("ContainerID").ToString
        Dim strQty As String = para("Qty").ToString

        Dim strQuery As String = "SELECT ta.specname,ta.specrevision,ta.specid,(CASE WHEN ta.dispatched IS NULL THEN 0 ELSE ta.dispatched END) AS dispatched," & vbCrLf & _
                                "   (CASE WHEN (" & strQty & " - (CASE WHEN ta.dispatched IS NULL THEN 0 ELSE ta.dispatched END)) > 0 THEN (" & strQty & " - (CASE WHEN ta.dispatched IS NULL THEN 0 ELSE ta.dispatched END)) ELSE 0 END) AS willdispatch," & vbCrLf & _
                                "   '" & strContainerID & "' AS containerid," & vbCrLf & _
                                "   (CASE WHEN ta.reportedqty IS NULL THEN 0 ELSE ta.reportedqty END) AS reportedqty,ta.sequence," & vbCrLf & _
                                "   (CASE WHEN ta.iskey = 1 THEN 'G' ELSE '' END) AS iskey" & vbCrLf & _
                                "FROM" & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT sb.specname,s.specrevision,s.specid,wfss.sequence,s.iskey," & vbCrLf & _
                                "       (SELECT SUM(qty) AS qty FROM workcenterdispatching wcd WHERE wcd.specid=s.specid AND wcd.dispatchtype = 0 AND wcd.containerid = '" & strContainerID & "') AS dispatched," & vbCrLf & _
                                "       (SELECT SUM(reportedqty) AS reportedqty FROM workcenterdispatching wcd WHERE wcd.specid=s.specid AND wcd.dispatchtotype = 1 AND wcd.containerid = '" & strContainerID & "') AS reportedqty" & vbCrLf & _
                                "   FROM spec s" & vbCrLf & _
                                "   LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "   RIGHT JOIN" & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT wfs.sequence," & vbCrLf & _
                                "           (CASE WHEN wfs.specid = '0000000000000000' THEN (SELECT revofrcdid FROM specbase WHERE specbaseid = wfs.specbaseid) ELSE specid END) AS specid" & vbCrLf & _
                                "       FROM workflowstep wfs" & vbCrLf & _
                                "       WHERE wfs.workflowid = '" & strWorkflowID & "'" & vbCrLf & _
                                "       ORDER BY wfs.sequence" & vbCrLf & _
                                "   ) wfss ON s.specid = wfss.specid" & vbCrLf & _
                                "   WHERE s.iscooperation = 2 OR s.iscooperation IS NULL" & vbCrLf

        ''排除检验工序
        Dim strCheckSpecKey As String = para("CheckSpecKey").ToString
        If strCheckSpecKey <> "" Then
            Dim arrayS As String() = strCheckSpecKey.Split(",")
            If arrayS.Length > 0 Then
                For i As Integer = 0 To arrayS.Length - 1
                    strQuery += "   AND sb.specname NOT LIKE '%" & arrayS(i).ToString & "%'" & vbCrLf
                Next
            End If
        End If

        ''排除清洗工序
        Dim strCleanSpecKey As String = para("CleanSpecKey").ToString
        If strCleanSpecKey <> "" Then
            Dim arrayC As String() = strCleanSpecKey.Split(",")
            If arrayC.Length > 0 Then
                For i As Integer = 0 To arrayC.Length - 1
                    strQuery += "   AND sb.specname NOT LIKE '%" & arrayC(i).ToString & "%'" & vbCrLf
                Next
            End If
        End If

        strQuery += "   AND s.specid NOT IN" & vbCrLf & _
                    "       (" & vbCrLf & _
                    "           SELECT i.specid FROM synergicspecinfo i" & vbCrLf & _
                    "           LEFT JOIN synergicspecvalidate ssv ON ssv.synergicspecvalidateid = i.synergicspecvalidateid" & vbCrLf & _
                    "           WHERE ssv.containerid = '" & strContainerID & "'" & vbCrLf & _
                    "       )" & vbCrLf & _
                    "   AND s.specid NOT IN" & vbCrLf & _
                    "       (" & vbCrLf & _
                    "           SELECT syi.specid FROM synergicinfo syi" & vbCrLf & _
                    "           WHERE syi.containerid = '" & strContainerID & "'" & vbCrLf & _
                    "       )" & vbCrLf & _
                    ") ta" & vbCrLf & _
                    "ORDER BY ta.sequence ASC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetSpecDispatchInfo(strQuery)
            DT.Columns.Add("ViewSetup")
            DT.Columns.Add("ViewDispatchAndReport")
            For i As Integer = 0 To DT.Rows.Count - 1
                Dim strHypeLink As String = ""
                strHypeLink = "<a href = '#' onclick = ""ViewSetUp('" & DT.Rows(i)("ContainerID").ToString & "','" & DT.Rows(i)("SpecID").ToString & "')"" style='font-size:12px; color:blue;'>查看生产准备</a>"
                DT.Rows(i)("ViewSetup") = strHypeLink
                strHypeLink = "<a href = '#' onclick = ""ViewDispatchAndReport('" & DT.Rows(i)("ContainerID").ToString & "','" & DT.Rows(i)("SpecID").ToString & "')"" style='font-size:12px; color:blue;'>查看派报工信息</a>"
                DT.Rows(i)("ViewDispatchAndReport") = strHypeLink
            Next

            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取Workflow的工序列表"
    Public Function GetSpecListByWorkflowID(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetSpecListByWorkflowID
        Dim strWorkflowID As String = para("WorkflowID").ToString

        Dim strQuery As String = "SELECT sb.specname,s.specrevision,s.specid,wfss.sequence," & vbCrLf & _
                                "   (CASE WHEN s.iskey = 1 THEN 'G' ELSE '' END) AS iskey" & vbCrLf & _
                                "FROM spec s" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "RIGHT JOIN" & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT wfs.sequence," & vbCrLf & _
                                "       (CASE WHEN wfs.specid = '0000000000000000' THEN (SELECT revofrcdid FROM specbase WHERE specbaseid = wfs.specbaseid) ELSE specid END) AS specid" & vbCrLf & _
                                "   FROM workflowstep wfs" & vbCrLf & _
                                "   WHERE wfs.workflowid = '" & strWorkflowID & "'" & vbCrLf & _
                                "   ORDER BY wfs.sequence" & vbCrLf & _
                                ") wfss ON s.specid = wfss.specid" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf
        Dim strType As String = para("OprType").ToString
        If strType = "MultiDispatch" Then
            strQuery += "AND s.iscooperation = 2 OR s.iscooperation IS NULL" & vbCrLf
        ElseIf strType = "DataPointInput" Then

        End If

        ''排除检验工序
        Dim strCheckSpecKey As String = para("CheckSpecKey").ToString
        If strCheckSpecKey <> "" Then
            Dim arrayS As String() = strCheckSpecKey.Split(",")
            If arrayS.Length > 0 Then
                For i As Integer = 0 To arrayS.Length - 1
                    strQuery += "AND sb.specname NOT LIKE '%" & arrayS(i).ToString & "%'" & vbCrLf
                Next
            End If
        End If

        ''排除清洗工序
        Dim strCleanSpecKey As String = para("CleanSpecKey").ToString
        If strCleanSpecKey <> "" Then
            Dim arrayC As String() = strCleanSpecKey.Split(",")
            If arrayC.Length > 0 Then
                For i As Integer = 0 To arrayC.Length - 1
                    strQuery += "   AND sb.specname NOT LIKE '%" & arrayC(i).ToString & "%'" & vbCrLf
                Next
            End If
        End If

        strQuery += "ORDER BY wfss.sequence ASC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetSpecListByWorkflowID(strQuery)

            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段的生产单元列表"
    Function GetTeamListByWorkCenter(ByVal strWorkCenterName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetTeamListByWorkCenter
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetTeamListByWorkCenter(strWorkCenterName)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取车间的工段列表"
    Function GetWorkCenterListByFactory(ByVal strFactoryName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetWorkCenterListByFactory
        Dim strQuery As String = "SELECT wc.workcentername,wc.workcenterid" & vbCrLf & _
                                "FROM workcenter wc" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = wc.factoryid" & vbCrLf & _
                                "WHERE f.factoryname = '" & strFactoryName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetWorkCenterListByFactory(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "根据批次ID列表获取工序派工信息"
    Function GetSpecDispatchByContainerList(ByVal dtContainerList As DataTable) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetSpecDispatchByContainerList
        Dim result As New uMESProcessResult

        Try
            ''批次ID列表
            Dim strContainerList As String = ""
            For i As Integer = 0 To dtContainerList.Rows.Count - 1
                Dim strContainerID As String = dtContainerList.Rows(i)("ContainerID").ToString
                If strContainerList = "" Then
                    strContainerList = "'" & strContainerID & "'"
                Else
                    strContainerList += ",'" & strContainerID & "'"
                End If
            Next

            Dim strQuery As String = ""
            ''获取批次在各工序已派工的数量
            strQuery = "SELECT SUM(wcd.qty) AS dispatchedqty,wcd.containerid,wcd.specid" & vbCrLf & _
                        "FROM workcenterdispatching wcd" & vbCrLf & _
                        "WHERE wcd.containerid IN (" & strContainerList & ")" & vbCrLf & _
                        "GROUP BY wcd.containerid,wcd.specid" & vbCrLf

            Dim dtQty As DataTable = dal.GetSpecDispatchByContainerList(strQuery)
            dtQty.TableName = "dtQty"

            ''获取批次在各工序已派工的零件编号
            strQuery = "SELECT wcdc.childcontainer,wcdc.sequence,wcdc.workcenterdispatchingid,wcd.containerid,wcd.specid" & vbCrLf & _
                        "FROM workcenterdispatchingchildcont wcdc" & vbCrLf & _
                        "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wcdc.workcenterdispatchingid" & vbCrLf & _
                        "WHERE wcd.containerid IN (" & strContainerList & ")" & vbCrLf

            Dim dtDispatchedChild As DataTable = dal.GetSpecDispatchByContainerList(strQuery)
            dtDispatchedChild.TableName = "dtDispatchedChild"

            ''获取批次的所有零件编号
            strQuery = "SELECT c.containername,c.parentcontainerid" & vbCrLf & _
                        "FROM container c" & vbCrLf & _
                        "WHERE c.status = 1 AND c.parentcontainerid IN (" & strContainerList & ")" & vbCrLf

            Dim dtAllChild As DataTable = dal.GetSpecDispatchByContainerList(strQuery)
            dtAllChild.TableName = "dtAllChild"

            Dim ds As New DataSet
            ds.Tables.Add(dtQty)
            ds.Tables.Add(dtDispatchedChild)
            ds.Tables.Add(dtAllChild)

            result.DBDataSet = ds
            result.Result = True
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "根据派工单ID列表获取已报工的零件编号"
    Function GetReportedProductNoByWCDList(ByVal dtWCDList As DataTable) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetReportedProductNoByWCDList
        Dim result As New uMESProcessResult

        Try
            ''派工单ID列表
            Dim strWCDList As String = ""
            For i As Integer = 0 To dtWCDList.Rows.Count - 1
                Dim strWCDID As String = dtWCDList.Rows(i)("WorkCenterDispatchingID").ToString
                If strWCDList = "" Then
                    strWCDList = "'" & strWCDID & "'"
                Else
                    strWCDList += ",'" & strWCDID & "'"
                End If
            Next

            Dim strQuery As String = ""
            ''获取派工单所有的零件编号
            strQuery = "SELECT wcdc.childcontainer,wcdc.sequence,wcdc.workcenterdispatchingid" & vbCrLf & _
                       "FROM workcenterdispatchingchildcont wcdc" & vbCrLf & _
                       "LEFT JOIN container c ON c.containername = wcdc.childcontainer" & vbCrLf & _
                       "WHERE c.status = 1" & vbCrLf & _
                       "AND wcdc.workcenterdispatchingid IN (" & strWCDList & ")" & vbCrLf

            Dim dtAllChild As DataTable = dal.GetProductNoByWorkCenterDispatch(strQuery)
            dtAllChild.TableName = "dtAllChild"

            ''获取派工单已报工的零件编号
            strQuery = "SELECT wrhc.childcontainer,wrhc.sequence,wrh.workcenterdispatchingid" & vbCrLf & _
                       "FROM workingreporthistorychildconta wrhc" & vbCrLf & _
                       "LEFT JOIN workingreporthistory wrh ON wrh.workingreporthistoryid = wrhc.workingreporthistoryid" & vbCrLf & _
                       "LEFT JOIN historymainline hml ON hml.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                       "WHERE hml.reversalstatus = 1" & vbCrLf & _
                       "AND wrh.workcenterdispatchingid IN (" & strWCDList & ")" & vbCrLf

            Dim dtReportedChild As DataTable = dal.GetProductNoByWorkCenterDispatch(strQuery)
            dtReportedChild.TableName = "dtReportedChild"

            Dim ds As New DataSet
            ds.Tables.Add(dtAllChild)
            ds.Tables.Add(dtReportedChild)

            result.DBDataSet = ds
            result.Result = True
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段的工人列表"
    Function GetEmployeeListByWorkCenter(ByVal strWorkCenterName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetEmployeeListByWorkCenter
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetEmployeeListByWorkCenter(strWorkCenterName)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取车间的互检人列表"
    Function GetMutualCheckEmployeeList(ByVal strFactoryName As String, ByVal strKey As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetMutualCheckEmployeeList
        Dim strQuery As String = "SELECT e.employeename,e.employeeid,e.fullname" & vbCrLf & _
                                "FROM employeerole er" & vbCrLf & _
                                "LEFT JOIN roledef r ON r.roleid = er.roleid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = er.employeeid" & vbCrLf & _
                                "LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = sv.factoryid" & vbCrLf & _
                                "WHERE r.rolename = '" & strKey & "'" & vbCrLf & _
                                "AND f.factoryname = '" & strFactoryName & "'" & vbCrLf & _
                                "GROUP BY e.employeename,e.employeeid,e.fullname" & vbCrLf & _
                                "ORDER BY e.fullname ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMutualCheckEmployeeList(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段的设备列表"
    Public Function GetResourceListByWorkCenter(ByVal strFactoryName As String, ByVal strEmployeeName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetResourceListByWorkCenter
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetResourceListByWorkCenter(strFactoryName, strEmployeeName)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取班组的设备列表"
    Public Function GetResourceListByTeam(ByVal teamName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetResourceListByTeam
        Dim result As New uMESProcessResult
        Try
            Dim strSql As String = "select r.* from resourcedef r" 'where t.teamname='" & teamName & "'"
            Dim DT As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
#End Region

#Region "得到设备对应的班组"
    Public Function GetTeamByResourceName(ByVal strResourceName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetTeamByResourceName

        Dim result As New uMESProcessResult
        Try
            Dim strSql As String = "select r.resourcename from resourcedef r "
            Dim DT As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
#End Region


#Region "获取生产单元的工人列表"
    Function GetEmployeeListByTeam(ByVal strTeamName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetEmployeeListByTeam
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetEmployeeListByTeam(strTeamName)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取生产单元的设备列表"
    Function GetResourceListByTeam(ByVal strTeamName As String, ByVal strFactoryName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetResourceListByTeam
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetResourceListByTeam(strTeamName, strFactoryName)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段派到小组的派工信息"
    Function GetWorkCenterDispatchingList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetWorkCenterDispatchingList
        Dim strQuery As String = "SELECT td.*," & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT SUM(wrh.qty) FROM workingreporthistory wrh" & vbCrLf & _
                                "   LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wrh.workcenterdispatchingid" & vbCrLf & _
                                "   LEFT jOIN historymainline hml ON hml.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                                "   WHERE hml.reversalstatus = 1 AND wcd.workcenterdispatchingid1 = td.workcenterdispatchingid" & vbCrLf & _
                                ") AS ReportedQty" & vbCrLf & _
                                "FROM" & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT mo.mfgordername,c.containername,pb.productname,p.description,p.productrevision,c.originalqty,c.qty,pc.prioritycodename," & vbCrLf & _
                                "       sb1.specname AS dispatchspecname,wcd.qty AS dispatchqty,wcd.plannedstartdate,wcd.plannedcompletiondate," & vbCrLf & _
                                "       sb.specname,s.specrevision,s1.specrevision AS dispatchspecrev,c.plannedstartdate AS cplannedstartdate,c.plannedcompletiondate AS cPlannedCompletionDate," & vbCrLf & _
                                "       wcd.workcenterdispatchingid,wcd.workcenterdispatchingname,pf.productfamilyname,pm.phasemarkname, c.containerid, " & vbCrLf & _
                                "       (SELECT SUM(qty) FROM workcenterdispatching WHERE workcenterdispatchingid1 = wcd.workcenterdispatchingid) AS DispatchedQty," & vbCrLf & _
                                "       (CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold," & vbCrLf & _
                                "       (CASE WHEN s1.iskey = 1 THEN 'G' ELSE '' END) AS iskey" & vbCrLf & _
                                "   FROM workcenterdispatching wcd" & vbCrLf & _
                                "   LEFT JOIN container c ON c.containerid = wcd.containerid" & vbCrLf & _
                                "   LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "   LEFT JOIN spec s ON s.specid = cu.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "   LEFT JOIN spec s1 ON s1.specid = wcd.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb1 ON sb1.specbaseid = s1.specbaseid" & vbCrLf & _
                                "   LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "   LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "   LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "   LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "   LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "   LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "   LEFT JOIN team t ON t.teamid = wcd.teamid" & vbCrLf & _
                                "   WHERE 1 = 1 AND wcd.dispatchtype = 0 AND wcd.dispatchtotype = 0 AND c.status = 1 AND c.containername <> c.containerid" & vbCrLf

        If para.Keys.Contains("TeamName") = True Then
            strQuery += "AND t.teamname = '" & para("TeamName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("MfgOrderName") = True Then
            strQuery += "AND LOWER(mo.mfgordername) LIKE '%" & para("MfgOrderName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("SecretLevel") = True Then
            strQuery += "AND (c.secretlevel <= " & para("SecretLevel").ToString & " OR c.secretlevel IS NULL)" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("PriorityCode") = True Then
            strQuery += "AND LOWER(pc.prioritycodename) = '" & para("PriorityCode").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("DispatchSpec") = True Then
            strQuery += "AND LOWER(sb1.specname) = '" & para("DispatchSpec").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        strQuery += ") td" & vbCrLf & _
                    "WHERE (dispatchqty - (CASE WHEN dispatchedqty IS NULL THEN 0 ELSE dispatchedqty END)) > 0" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段长派到小组的派工信息"
    Function GetToTeamList(ByVal strContainerID As String, ByVal strSpecID As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetToTeamList
        Dim strQuery As String = "SELECT t.teamname,wcd.qty,e.employeename,e.fullname,wcd.dispatchdate,wcd.plannedstartdate," & vbCrLf & _
                                "   wcd.plannedcompletiondate,wcd.workcenterdispatchingname AS WCDName" & vbCrLf & _
                                "FROM workcenterdispatching wcd" & vbCrLf & _
                                "LEFT JOIN team t ON t.teamid = wcd.teamid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = wcd.dispatchemployeeid" & vbCrLf & _
                                "WHERE wcd.dispatchtype = 0 AND wcd.dispatchtotype = 0" & vbCrLf & _
                                "AND wcd.containerid = '" & strContainerID & "' AND wcd.specid = '" & strSpecID & "'" & vbCrLf & _
                                "ORDER BY wcd.dispatchdate ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetToTeamList(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取派到设备的派工信息"
    Function GetToResourceList(ByVal strContainerID As String, ByVal strSpecID As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetToResourceList
        Dim strQuery As String = "SELECT (CASE WHEN wcd1.workcenterdispatchingname IS NULL THEN '派到设备' ELSE wcd1.workcenterdispatchingname END) AS WCDName," & vbCrLf & _
                                "   r.resourcename,r.resourceename,r.resourcenum,e.employeename AS mainemployeename,e.fullname AS mainfullname," & vbCrLf & _
                                "   e1.employeename AS minoremployeename,e1.fullname AS minorfullname,e2.employeename AS dispatchemployeename,e2.fullname AS dispatchfullname," & vbCrLf & _
                                "   wcd.qty,wcd.dispatchdate,wcd.plannedstartdate,wcd.plannedcompletiondate,wcd.workcenterdispatchingname," & vbCrLf & _
                                "   e3.employeename AS receiveemployeename,e3.fullname AS receivefullname,wcd.receivedate" & vbCrLf & _
                                "FROM workcenterdispatching wcd" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd1 ON wcd1.workcenterdispatchingid = wcd.workcenterdispatchingid1" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = wcd.resourceid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = wcd.mainmfgemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e1 ON e1.employeeid = wcd.minormfgemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e2 ON e2.employeeid = wcd.dispatchemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e3 ON e3.employeeid = wcd.receiveemployeeid" & vbCrLf & _
                                "WHERE wcd.dispatchtotype = 1" & vbCrLf & _
                                "AND wcd.containerid = '" & strContainerID & "' AND wcd.specid = '" & strSpecID & "'" & vbCrLf & _
                                "ORDER BY wcd.dispatchdate ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetToResourceList(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取报工信息"
    Function GetWorkingReportList(ByVal strContainerID As String, ByVal strSpecID As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetWorkingReportList
        Dim strQuery As String = "SELECT wcd.workcenterdispatchingname,e.employeename,e.fullname,wrh.qty,r.resourcename," & vbCrLf & _
                                "   r.resourceename,r.resourcenum,wrh.ncprogramname,hml.txndate,wrh.realcompletiondate,wrh.workingreporthistoryid" & vbCrLf & _
                                "FROM workingreporthistory wrh" & vbCrLf & _
                                "LEFT JOIN historymainline hml ON hml.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wrh.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = hml.employeeid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = wrh.resourceid" & vbCrLf & _
                                "WHERE hml.reversalstatus = 1" & vbCrLf & _
                                "AND wcd.containerid = '" & strContainerID & "' AND wcd.specid = '" & strSpecID & "'" & vbCrLf & _
                                "ORDER BY hml.txndate ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetWorkingReportList(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取批次的质量记载五单信息"
    Function GetFiveBillsInfoByContainer(ByVal strContainerName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetFiveBillsInfoByContainer
        Dim strQuery As String = "SELECT f.fivebillno,ft.fivebilltypename,f.conclusion" & vbCrLf & _
                                "FROM fivebillsinfo f" & vbCrLf & _
                                "LEFT JOIN fivebilltype ft ON ft.fivebilltypeid = f.fivebilltypeid" & vbCrLf & _
                                "LEFT JOIN qualityrecord q ON q.qualityrecordid = f.qualityrecordid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = q.containerid" & vbCrLf & _
                                "WHERE c.containername = '" & strContainerName & "'" & vbCrLf & _
                                "ORDER BY f.fivebillno ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetFiveBillsInfoByContainer(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取报工信息"
    Function GetWorkingReportListByWCDName(ByVal strWCDName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetWorkingReportListByWCDName
        Dim strQuery As String = "SELECT wcd.workcenterdispatchingname,e.employeename,e.fullname,wrh.qty,r.resourcename," & vbCrLf & _
                                "   r.resourceename,r.resourcenum,wrh.ncprogramname,hml.txndate,wrh.realcompletiondate,wrh.workingreporthistoryid," & vbCrLf & _
                                "   sb.specname,s.specrevision,s.specid,c.containername" & vbCrLf & _
                                "FROM workingreporthistory wrh" & vbCrLf & _
                                "LEFT JOIN historymainline hml ON hml.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = hml.containerid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wrh.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = wcd.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = hml.employeeid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = wrh.resourceid" & vbCrLf & _
                                "WHERE hml.reversalstatus = 1" & vbCrLf & _
                                "AND wcd.workcenterdispatchingname = '" & strWCDName & "'" & vbCrLf & _
                                "ORDER BY hml.txndate ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetWorkingReportList(strQuery)

            DT.Columns.Add("ViewProductNo")
            For i As Integer = 0 To DT.Rows.Count - 1
                Dim strHypeLink As String = "<a href = '#' onclick = ""openProductNowins('" & DT.Rows(i)("WorkingReportHistoryID").ToString & "','" & DT.Rows(i)("ContainerName").ToString & "')"" style='font-size:12px; color:blue;'>查看零件编号</a>"
                DT.Rows(i)("ViewProductNo") = strHypeLink
            Next

            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取报工信息"
    Function GetWorkingReportListByContainerName(ByVal strContainerName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetWorkingReportListByContainerName
        Dim strQuery As String = "SELECT wcd.workcenterdispatchingname,e.employeename,e.fullname,wrh.qty,r.resourcename," & vbCrLf & _
                                "   r.resourceename,r.resourcenum,wrh.ncprogramname,hml.txndate,wrh.realcompletiondate,wrh.workingreporthistoryid," & vbCrLf & _
                                "   sb.specname,s.specrevision,s.specid" & vbCrLf & _
                                "FROM workingreporthistory wrh" & vbCrLf & _
                                "LEFT JOIN historymainline hml ON hml.historymainlineid = wrh.historymainlineid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wrh.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = wcd.containerid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = wcd.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = hml.employeeid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = wrh.resourceid" & vbCrLf & _
                                "WHERE hml.reversalstatus = 1" & vbCrLf & _
                                "AND c.containername = '" & strContainerName & "'" & vbCrLf & _
                                "ORDER BY hml.txndate ASC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetWorkingReportList(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取小组派到设备的派工信息"
    Function GetTeamDispatchingList(ByVal strWCDID As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetTeamDispatchingList
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetTeamDispatchingByWCD(strWCDID)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工段派工信息For改派"
    Function GetWorkCenterDispatchingForChange(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetWorkCenterDispatchingForChange
        ''单件单卡同批次标记
        Dim strTogetherFlag As String = ""
        If para.Keys.Contains("ContainerName") = True Then
            Dim strContainerName As String = para("ContainerName").ToString
            Dim r As uMESProcessResult = GetContainerInfo(strContainerName)
            If r.Result = True Then
                Try
                    strTogetherFlag = r.DBTable.Rows(0)("TogetherFlag").ToString
                Catch ex As Exception
                    strTogetherFlag = ""
                End Try
            End If
        End If

        Dim strQuery As String = "SELECT mo.mfgordername,c.containername,sb.specname,t.teamname,wcd.dispatchdate," & vbCrLf & _
                                "e.employeename AS mainmfgemployee,e.fullname AS mainfullname," & vbCrLf & _
                                "e1.employeename AS minormfgemployee,e1.fullname AS minorfullname," & vbCrLf & _
                                "r.resourcename,r.resourceename,r.resourcenum,e2.employeename AS dispatchemployee,e2.fullname AS dispatchfullname," & vbCrLf & _
                                "wcd.qty,wcd.reportedqty,wcd.plannedstartdate,wcd.plannedcompletiondate," & vbCrLf & _
                                "wcd.workcenterdispatchingname,pb.productname,p.description,c.OriginalQty,c.qty AS CurrentQty," & vbCrLf & _
                                "(CASE WHEN wcd.status = 0 THEN '已派工' WHEN wcd.status = 1 THEN '已接收' WHEN wcd.status = 2 THEN '已报工' WHEN wcd.status = 3 THEN '已完成' WHEN wcd.status = 4 THEN '已关闭' ELSE TO_CHAR(wcd.status) END) AS status," & vbCrLf & _
                                "wcd1.workcenterdispatchingname AS parentname,wcd.dispatchtotype,wcd.dispatchtype,pf.productfamilyname,pm.phasemarkname," & vbCrLf & _
                                "(CASE WHEN s.iskey = 1 THEN 'G' ELSE '' END) AS iskey," & vbCrLf & _
                                "(CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold" & vbCrLf & _
                                "FROM workcenterdispatching wcd" & vbCrLf & _
                                "LEFT JOIN team t ON t.teamid = wcd.teamid" & vbCrLf & _
                                "LEFT JOIN workcenter wc ON wc.workcenterid = wcd.workcenterid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = wcd.containerid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = wcd.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = wcd.resourceid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = wcd.mainmfgemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e1 ON e1.employeeid = wcd.minormfgemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e2 ON e2.employeeid = wcd.dispatchemployeeid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd1 ON wcd1.workcenterdispatchingid = wcd.workcenterdispatchingid1" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf

        If para.Keys.Contains("Status") = True Then
            strQuery += "AND wcd.status = " & para("Status").ToString & vbCrLf
        End If

        If para.Keys.Contains("HaveChild") = True Then
            Dim strHaveChild As String = para("HaveChild").ToString.ToLower
            If strHaveChild = "false" Then
                strQuery += "AND wcd.workcenterdispatchingid NOT IN (SELECT workcenterdispatchingid1 FROM workcenterdispatching WHERE workcenterdispatchingid1 IS NOT NULL)" & vbCrLf
            End If
        End If

        If para.Keys.Contains("WorkCenterName") = True Then
            strQuery += "AND wc.workcentername = '" & para("WorkCenterName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("MfgOrderName") = True Then
            strQuery += "AND LOWER(mo.mfgordername) LIKE '%" & para("MfgOrderName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            If strTogetherFlag = "" Then
                strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
            Else
                strQuery += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
            End If
        End If
        If para.Keys.Contains("SecretLevel") = True Then
            strQuery += "AND (c.secretlevel <= " & para("SecretLevel").ToString & " OR c.secretlevel IS NULL)" & vbCrLf
        End If
        If para.Keys.Contains("SearchContainerName") = True Then
            strQuery += "AND LOWER(c.containername) LIKE '%" & para("SearchContainerName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("SpecName") = True Then
            strQuery += "AND LOWER(sb.specname) LIKE '%" & para("SpecName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("TeamName") = True Then
            strQuery += "AND t.teamname = '" & para("TeamName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        strQuery += "ORDER BY pb.productname ASC,c.containername ASC" & vbCrLf & _
                    "" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取小组派工信息For改派"
    Function GetTeamDispatchingForChange(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetTeamDispatchingForChange
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetTeamDispatchingForChange(para)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "创建派工单"
    Function CreateDispatching(ByVal para As DispatchingPara, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMES_bjDispatchIBusiness.CreateDispatching
        Dim strDispatchType As DispatchTypeEnum.DispatchTypeEnum = para.DispatchType
        Dim dtChildContainer As DataTable = para.ChildContainer

        Dim dataEntityList As New List(Of ClientAPIEntity)

        ''派工单号
        Dim dataEntity As New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = Now.ToString("yyyyMMddHHmmssfff")
        dataEntityList.Add(dataEntity)

        ''跟踪卡号
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Container"
        dataEntity.ClientDataValue = para.ContainerName
        dataEntity.ClientOtherValue = "Lot"
        dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
        dataEntityList.Add(dataEntity)


        ''工序
        Dim s As RevisionedObjectRef = para.Spec
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Spec"
        dataEntity.ClientDataValue = s.__name
        dataEntity.ClientDataVersion = s.__rev
        dataEntity.ClientDataIsROR = s.__useROR
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntityList.Add(dataEntity)

        ''数量
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Qty"
        dataEntity.ClientDataValue = para.Qty
        dataEntityList.Add(dataEntity)

        ''计划开始日期
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedStartDate"
        dataEntity.ClientDataValue = para.PlannedStartDate
        dataEntityList.Add(dataEntity)

        ''计划完成日期
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedCompletionDate"
        dataEntity.ClientDataValue = para.PlannedCompletionDate
        dataEntityList.Add(dataEntity)

        ''派工者
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "DispatchEmployee"
        dataEntity.ClientDataValue = para.DispatchEmployee
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntityList.Add(dataEntity)

        ''派工时间
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "DispatchDate"
        dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
        dataEntityList.Add(dataEntity)

        ''DispatchToType
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "DispatchToType"
        dataEntity.ClientDataValue = para.DispatchToType
        dataEntityList.Add(dataEntity)

        ''工段
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "WorkCenter"
        dataEntity.ClientDataValue = para.WorkCenterName
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntityList.Add(dataEntity)

        ''工段长派工
        If strDispatchType = DispatchTypeEnum.DispatchTypeEnum.WorkCenterDispatch Then
            ''派工类型
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "DispatchType"
            dataEntity.ClientDataValue = "0"
            dataEntityList.Add(dataEntity)
        ElseIf strDispatchType = DispatchTypeEnum.DispatchTypeEnum.TeamDispatch Then

            ''派工类型
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "DispatchType"
            dataEntity.ClientDataValue = "1"
            dataEntityList.Add(dataEntity)
        End If

        ''Team
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Team"
        dataEntity.ClientDataValue = para.Team
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntityList.Add(dataEntity)

        ''设备
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Resource"
        dataEntity.ClientDataValue = para.Resource
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntityList.Add(dataEntity)

        ''主岗
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "MainMfgEmployee"
        dataEntity.ClientDataValue = para.MainMfgEmployee
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntityList.Add(dataEntity)

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.CreateDispatching(dataEntityList, dtChildContainer, strInfo)

            If result.Result = True Then

                'Dim strSql As String = "update containerattrhistorydetail t set t.attributevalue_after=NVL(t.attributevalue_after, 0) +" & para.Qty & " where t.containerattrhistorydetailid=(" & _
                '                       "select t1.containerattrhistorydetailid from containerattrhistorydetail t1 " & _
                '                       "left join containerattrmainthistory h on h.containerattrmainthistoryid=t1.containerattrmainthistoryid " & _
                '                       "left join historymainline hm on hm.historymainlineid=h.historymainlineid " & _
                '                       "left join container c on c.containerid=hm.containerid " & _
                '                       "where c.containername='" & para.ContainerName & "' and t1.attributename_after='DispatchQty')"
                'DatabaseHelper.ExecuteSql(strSql)

                'Dim s1 As RevisionedObjectRef = para.Spec
                'strSql = "update containerophistory  set  dispatchqty =NVL(dispatchqty, 0) +" & para.Qty & " where contanername ='" & para.ContainerName & "' and specname ='" & s1.__name & "'"
                'DatabaseHelper.ExecuteSql(strSql)
            End If

            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result

    End Function
#End Region

#Region "修改派工单"
    Function UpdateDispatching(ByVal para As DispatchingPara, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMES_bjDispatchIBusiness.UpdateDispatching
        Dim strDispatchType As DispatchTypeEnum.DispatchTypeEnum = para.DispatchType
        Dim dtChildContainer As DataTable = para.ChildContainer
        Dim dtNewChildContainer As DataTable = para.NewChildContainer

        Dim dataEntityList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        ''改派
        If para.OperationType = OperationTypeEnum.OperationTypeEnum.ChangeDispatch Then

            ''数量
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "Qty"
            dataEntity.ClientDataValue = para.Qty
            dataEntityList.Add(dataEntity)

            ''计划开始日期
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "PlannedStartDate"
            dataEntity.ClientDataValue = para.PlannedStartDate
            dataEntityList.Add(dataEntity)

            ''计划完成日期
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "PlannedCompletionDate"
            dataEntity.ClientDataValue = para.PlannedCompletionDate
            dataEntityList.Add(dataEntity)

            ''派工者
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "DispatchEmployee"
            dataEntity.ClientDataValue = para.DispatchEmployee
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)

            ''派工时间
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "DispatchDate"
            dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
            dataEntityList.Add(dataEntity)

            If para.DispatchToType = DispatchToTypeEnum.DispatchToTypeEnum.ToTeam Then

                ''Team
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "Team"
                dataEntity.ClientDataValue = para.Team
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

                'zll 20151217 清空资源、主岗、副岗
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "Resource"
                dataEntity.ClientDataValue = ""
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

                ''主岗
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "MainMfgEmployee"
                dataEntity.ClientDataValue = ""
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

                ''副岗
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "MinorMfgEmployee"
                dataEntity.ClientDataValue = ""
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

            ElseIf para.DispatchToType = DispatchToTypeEnum.DispatchToTypeEnum.ToResource Then

                'zll 20151217 清空班组
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "Team"
                dataEntity.ClientDataValue = ""
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

                ''主岗
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "MainMfgEmployee"
                dataEntity.ClientDataValue = para.MainMfgEmployee
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

                ''副岗
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "MinorMfgEmployee"
                dataEntity.ClientDataValue = para.MinorMfgEmployee
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

                ''设备
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "Resource"
                dataEntity.ClientDataValue = para.Resource
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)

            End If

        ElseIf para.OperationType = OperationTypeEnum.OperationTypeEnum.Receive Then ''任务接收

            ''接收者
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ReceiveEmployee"
            dataEntity.ClientDataValue = para.ReceiveEmployee
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)

            ''接收时间
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "ReceiveDate"
            dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
            dataEntityList.Add(dataEntity)

            ''数量
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientDataName = "Status"
            dataEntity.ClientDataValue = para.DispatchStatus
            dataEntityList.Add(dataEntity)
        ElseIf para.OperationType = OperationTypeEnum.OperationTypeEnum.Mark Then ''修改零件编号

        End If

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.UpdateDispatching(para.Name, dataEntityList, dtChildContainer, dtNewChildContainer, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取派到设备/工人的派工信息"
    Function GetToEmployeeDispatchingList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetToEmployeeDispatchingList
        ''单件单卡同批次标记
        Dim strTogetherFlag As String = ""
        If para.Keys.Contains("ContainerName") = True Then
            Dim strContainerName As String = para("ContainerName").ToString
            Dim r As uMESProcessResult = GetContainerInfo(strContainerName)
            If r.Result = True Then
                Try
                    strTogetherFlag = r.DBTable.Rows(0)("TogetherFlag").ToString
                Catch ex As Exception
                    strTogetherFlag = ""
                End Try
            End If
        End If

        Dim strQuery As String = "SELECT mt.*," & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT fullname" & vbCrLf & _
                                "       FROM" & vbCrLf & _
                                "       (" & vbCrLf & _
                                "           SELECT e.employeeid,e.employeename,e.fullname,wcd.containerid,wcd.specid" & vbCrLf & _
                                "           FROM workcenterdispatching wcd" & vbCrLf & _
                                "           LEFT JOIN employee e ON e.employeeid = wcd.dispatchemployeeid" & vbCrLf & _
                                "           WHERE wcd.dispatchtype = 0" & vbCrLf & _
                                "           ORDER BY wcd.dispatchdate ASC" & vbCrLf & _
                                "       ) wcd2" & vbCrLf & _
                                "       WHERE ROWNUM = 1 AND wcd2.containerid = mt.containerid AND wcd2.specid = mt.nextSpecid" & vbCrLf & _
                                "   ) AS nextDispatchEmployee" & vbCrLf & _
                                "FROM" & vbCrLf & _
                                "(" & vbCrLf & _
                                "  SELECT mo.mfgordername,c.containername,pb.productname,p.productrevision,p.description,p.productid,c.originalqty,c.qty,pc.prioritycodename," & vbCrLf & _
                                "       sb1.specname AS dispatchspecname,wcd.qty AS dispatchqty,wcd.plannedstartdate,wcd.plannedcompletiondate," & vbCrLf & _
                                "       sb.specname,s.specrevision,s1.specrevision AS dispatchspecrev,c.plannedstartdate AS cplannedstartdate,c.plannedcompletiondate AS cPlannedCompletionDate," & vbCrLf & _
                                "       wcd.workcenterdispatchingid,wcd.workcenterdispatchingname,r.resourcename,r.resourcenum,r.resourceename," & vbCrLf & _
                                "       e.employeename AS mainemployee,e.fullname AS mainfullname," & vbCrLf & _
                                "       e1.employeename AS minoremployee,e1.fullname AS minorfullname,wcd.reportedqty," & vbCrLf & _
                                "       DECODE(wcd.status,'0','已派工','1','已接收','2','已报工','3','已完成','4','已关闭') AS status" & vbCrLf & _
                                "       ,c.containerid,wcd.specid,pf.productfamilyname,pm.phasemarkname,c.togetherflag," & vbCrLf & _
                                "       (" & vbCrLf & _
                                "           CASE WHEN" & vbCrLf & _
                                "               (" & vbCrLf & _
                                "                   SELECT qr.qualityrecordid FROM productnoinfo pni" & vbCrLf & _
                                "                   LEFT JOIN qualityrecord qr ON qr.qualityrecordid = pni.qualityrecordid" & vbCrLf & _
                                "                   WHERE ',' || pni.containerlist || ',' LIKE '%,' || sb.specname || ',%' AND ROWNUM = 1 AND qr.containerid = c.containerid" & vbCrLf & _
                                "               ) IS NULL THEN '0' ELSE '1' END" & vbCrLf & _
                                "       ) AS chaochachuli," & vbCrLf & _
                                "       (CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold," & vbCrLf & _
                                "       (CASE WHEN s1.iskey = 1 THEN 'G' ELSE '' END) AS iskey," & vbCrLf & _
                                "       (" & vbCrLf & _
                                "          CASE WHEN" & vbCrLf & _
                                "              (" & vbCrLf & _
                                "                  SELECT SUM(wcd1.reportedqty) FROM workcenterdispatching wcd1 WHERE wcd1.containerid = wcd.containerid AND wcd1.specid = wcd.specid" & vbCrLf & _
                                "              ) IS NULL THEN 0 ELSE" & vbCrLf & _
                                "              (" & vbCrLf & _
                                "                  SELECT SUM(wcd1.reportedqty) FROM workcenterdispatching wcd1 WHERE wcd1.containerid = wcd.containerid AND wcd1.specid = wcd.specid" & vbCrLf & _
                                "              ) END" & vbCrLf & _
                                "       ) AS contreportedqty," & vbCrLf & _
                                "       (" & vbCrLf & _
                                "           SELECT wfs2.specid" & vbCrLf & _
                                "           FROM" & vbCrLf & _
                                "               (" & vbCrLf & _
                                "                   SELECT wfs1.workflowid,wfs1.sequence,(CASE WHEN wfs1.specid = '0000000000000000' THEN (SELECT sb1.revofrcdid FROM specbase sb1 WHERE sb1.specbaseid = wfs1.specbaseid) ELSE wfs1.specid END) AS specid" & vbCrLf & _
                                "                   FROM workflowstep wfs1" & vbCrLf & _
                                "                   ORDER BY wfs1.sequence ASC" & vbCrLf & _
                                "               ) wfs2" & vbCrLf & _
                                "           WHERE ROWNUM = 1 AND wfs2.sequence > wfs.sequence AND wfs2.workflowid = wfs.workflowid" & vbCrLf & _
                                "       ) AS nextSpecid," & vbCrLf & _
                                "       ROWNUM AS rowIndex" & vbCrLf & _
                                "   FROM workcenterdispatching wcd" & vbCrLf & _
                                "   LEFT JOIN container c ON c.containerid = wcd.containerid" & vbCrLf & _
                                "   LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "   LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "   LEFT JOIN spec s ON s.specid = cu.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "   LEFT JOIN spec s1 ON s1.specid = wcd.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb1 ON sb1.specbaseid = s1.specbaseid" & vbCrLf & _
                                "   LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "   LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "   LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "   LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "   LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "   LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "   LEFT JOIN employee e ON e.employeeid = wcd.mainmfgemployeeid" & vbCrLf & _
                                "   LEFT JOIN employee e1 ON e1.employeeid = wcd.minormfgemployeeid" & vbCrLf & _
                                "   LEFT JOIN resourcedef r ON r.resourceid = wcd.resourceid" & vbCrLf & _
                                "   LEFT JOIN workcenter w ON w.workcenterid = wcd.workcenterid" & vbCrLf & _
                                "   WHERE 1 = 1 AND wcd.status <> 3 AND wcd.status <> 4 AND c.status = 1 AND c.containername <> c.containerid" & vbCrLf & _
                                "   AND c.parentcontainerid IS NULL" & vbCrLf & _
                                "   AND wcd.specid = cu.specid" & vbCrLf   'AND wcd.dispatchtotype = 1 

        If para.Keys.Contains("WorkCenterName") = True Then
            'strQuery += "AND w.workcentername = '" & para("WorkCenterName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("MfgOrderName") = True Then
            strQuery += "AND LOWER(mo.mfgordername) LIKE '%" & para("MfgOrderName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            If strTogetherFlag = "" Then
                strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
            Else
                strQuery += "AND LOWER(c.togetherflag) = '" & strTogetherFlag.ToLower & "'" & vbCrLf
            End If
        End If
        If para.Keys.Contains("SecretLevel") = True Then
            strQuery += "AND (c.secretlevel <= " & para("SecretLevel").ToString & " OR c.secretlevel IS NULL)" & vbCrLf
        End If
        If para.Keys.Contains("SearchContainerName") = True Then
            strQuery += "AND LOWER(c.containername) LIKE '%" & para("SearchContainerName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("PriorityCode") = True Then
            strQuery += "AND LOWER(pc.prioritycodename) = '" & para("PriorityCode").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("DispatchSpec") = True Then
            strQuery += "AND LOWER(sb1.specname) LIKE '%" & para("DispatchSpec").ToString.ToLower & "%'" & vbCrLf
        End If

        ''区分普通工人、清洗工人、配套出库工人
        If para.Keys.Contains("WorkerType") = True Then
            Dim strWorkerType As String = para("WorkerType").ToString
            Dim strWorkerTypeKey As String = para("WorkerTypeKey").ToString
            Dim array As String() = strWorkerTypeKey.Split(",")

            Select Case strWorkerType
                Case "Normal" ''普通工人
                    If array.Length > 0 Then
                        For i As Integer = 0 To array.Length - 1
                            If array(i).Trim() <> "" Then
                                strQuery += "AND LOWER(sb1.specname) NOT LIKE '%" & array(i).ToLower & "%'" & vbCrLf
                            End If
                        Next
                    End If
                Case "CleanWorker" ''清洗工人
                    If array.Length > 0 Then
                        strQuery += "AND (" & vbCrLf
                        For i As Integer = 0 To array.Length - 1
                            If i = 0 Then
                                strQuery += "   (LOWER(sb1.specname) LIKE '%" & array(i).ToLower & "%')" & vbCrLf
                            Else
                                strQuery += "   OR (LOWER(sb1.specname) LIKE '%" & array(i).ToLower & "%')" & vbCrLf
                            End If
                        Next
                        strQuery += ")" & vbCrLf
                    End If
                Case "MaterialOutWorker" ''配套出库工人
                    'If array.Length > 0 Then
                    '    strQuery += "AND (" & vbCrLf
                    '    For i As Integer = 0 To array.Length - 1

                    '        If i = 0 Then
                    '            strQuery += "   (LOWER(sb1.specname) LIKE '%" & array(i).ToLower & "%')" & vbCrLf
                    '        Else
                    '            strQuery += "   OR (LOWER(sb1.specname) LIKE '%" & array(i).ToLower & "%')" & vbCrLf
                    '        End If
                    '    Next
                    '    strQuery += ")" & vbCrLf
                    'End If
            End Select
        End If

        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND wcd.plannedcompletiondate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EmployeeName") = True Then
            If para.Keys.Contains("SearchType") = True Then
                Dim strSearchType As String = para("SearchType").ToString
                If strSearchType = "Init" Then
                    strQuery += "AND (LOWER(e.employeename) = LOWER('" & para("EmployeeName").ToString & "') OR LOWER(e1.employeename) = LOWER('" & para("EmployeeName").ToString & "'))" & vbCrLf
                Else
                    strQuery += "AND (LOWER(e.employeename) = LOWER('" & para("EmployeeName").ToString & "') OR LOWER(e1.employeename) = LOWER('" & para("EmployeeName").ToString & "')" & vbCrLf & _
                                "   OR LOWER('" & para("EmployeeName").ToString & "') IN (" & vbCrLf & _
                                "       SELECT LOWER(er.employeename)" & vbCrLf & _
                                "       FROM employeeresourcelist erl" & vbCrLf & _
                                "       LEFT JOIN employee er ON er.employeeid = erl.employeeid" & vbCrLf & _
                                "       LEFT JOIN resourcedef re ON re.resourceid = erl.resourcelistid" & vbCrLf & _
                                "       WHERE erl.resourcelistid = wcd.resourceid))" & vbCrLf
                End If
            Else
                ''不做处理
            End If
        End If

        Dim intCurrentPageIndex As Integer = Convert.ToInt32(para("CurrentPageIndex").ToString)
        Dim intPageSize As Integer = Convert.ToInt32(para("PageSize").ToString)

        strQuery += "ORDER BY c.containername DESC" & vbCrLf & _
                    ") mt" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim dtAll As DataTable = dal.GetToEmployeeDispatchingList(strQuery)

            strQuery += "WHERE rowIndex BETWEEN " & intPageSize * (intCurrentPageIndex - 1) + 1 & " AND " & intPageSize * intCurrentPageIndex & vbCrLf
            Dim dtCurrentPage As DataTable = dal.GetToEmployeeDispatchingList(strQuery)

            Dim DT As DataTable = dtCurrentPage
            DT.Columns.Add("ViewQuality")
            DT.Columns.Add("ViewSetup")
            DT.Columns.Add("IsCC")
            DT.Columns.Add("NextDispatch")
            For i As Integer = 0 To DT.Rows.Count - 1
                Dim strHypeLink As String = "<a href = '#' onclick = ""OpenWindow('',500,740)"" style='font-size:12px; color:blue;'>质量记载查看</a>"
                DT.Rows(i)("ViewQuality") = strHypeLink
                strHypeLink = "<a href = '#' onclick = ""ViewSetUp('" & DT.Rows(i)("ContainerID").ToString & "','" & DT.Rows(i)("SpecID").ToString & "')"" style='font-size:12px; color:blue;'>查看生产准备</a>"
                DT.Rows(i)("ViewSetup") = strHypeLink

                Dim strCC As String = DT.Rows(i)("ChaoChaChuLi").ToString
                Dim strImg As String = ""
                If strCC = "1" Then
                    strImg = "<a href = '#' title=""点击查看零件编号"" onclick = ""ViewProductNo('" & DT.Rows(i)("ContainerID").ToString & "','" & DT.Rows(i)("SpecID").ToString & "')""><img src=""../../../images/Red16.png"" style=""border:none;"" /></a>"
                End If
                strHypeLink = strImg
                DT.Rows(i)("IsCC") = strHypeLink
                If Not IsDBNull(DT.Rows(i)("nextDispatchEmployee")) Then
                    strHypeLink = "<a href = '#' onclick = ""ViewDispatchAndReport('" & DT.Rows(i)("ContainerID").ToString & "','" & DT.Rows(i)("NextSpecID").ToString & "')"" style='font-size:12px; color:blue;'>" & DT.Rows(i)("nextDispatchEmployee").ToString & "</a>"
                    DT.Rows(i)("NextDispatch") = strHypeLink
                Else
                    DT.Rows(i)("NextDispatch") = ""
                End If
            Next

            result.Result = True
            result.DBTable = DT
            result.RowCount = dtAll.Rows.Count
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region







#Region "获取转工序处理的零件编号"
    Function GetCCProductNo(ByVal strContainerID As String, ByVal strSpecID As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetCCProductNo
        Dim strQuery As String = "SELECT pi.productno,pi.containerlist,qr.containerid,c.containername" & vbCrLf & _
                                "FROM productnoinfo pi" & vbCrLf & _
                                "LEFT JOIN qualityrecord qr ON qr.qualityrecordid = pi.qualityrecordid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = qr.specid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = qr.containerid" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf & _
                                "AND qr.containerid = '" & strContainerID & "'" & vbCrLf & _
                                "AND ',' || pi.containerlist || ',' LIKE '%,' ||" & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT sb.specname" & vbCrLf & _
                                "       FROM spec s" & vbCrLf & _
                                "       LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "       WHERE s.specid = '" & strSpecID & "'" & vbCrLf & _
                                ")" & vbCrLf & _
                                "|| ',%'" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoForMark(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取跟踪卡的零件编号"
    Function GetProductNoForMark(ByVal strContainerName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetProductNoForMark
        Dim strQuery As String = "SELECT c.containername," & vbCrLf & _
                                "   (CASE WHEN c.status = 0 THEN '已删除' WHEN c.status = 1 THEN '正常'  WHEN c.status = 2 THEN '已关闭'  WHEN c.status = 3 THEN 'Shipped'  WHEN c.status = 4 THEN '已消耗' END) AS status" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN container c1 ON c1.containerid = c.parentcontainerid" & vbCrLf & _
                                "WHERE c1.containername = '" & strContainerName & "'" & vbCrLf & _
                                "ORDER BY c.containername asc " & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoForMark(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "根据物料名称获取合格证号列表"
    Function GetQualificationListByMaterialName(ByVal strMaterialName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetQualificationListByMaterialName
        Dim strQuery As String = "SELECT m.materialinfoname FROM materialinfo m" & vbCrLf & _
                                "WHERE m.qualificationnum IS NOT NULL AND m.materialname = '" & strMaterialName & "'" & vbCrLf & _
                                "GROUP BY m.materialinfoname" & vbCrLf & _
                                "ORDER BY m.materialinfoname ASC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetQualificationListByMaterialName(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取派工单的零件编号"
    Function GetProductNoByDispatch(ByVal strDispachName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetProductNoByDispatch
        Dim strQuery As String = "SELECT wcd.workcenterdispatchingname,c.childcontainer FROM workcenterdispatchingchildcont c" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = c.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN container cc ON cc.containername = c.childcontainer" & vbCrLf & _
                                "WHERE cc.status = 1 AND wcd.workcenterdispatchingname = '" & strDispachName & "'" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoByDispach(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取报工单的零件编号"
    Function GetProductNoByReport(ByVal strReportID As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetProductNoByReport
        Dim strQuery As String = "SELECT childcontainer FROM workingreporthistorychildconta" & vbCrLf & _
                                "WHERE workingreporthistoryid = '" & strReportID & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoByReport(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取指定批次指定工序上的已派工零件编号"
    Function GetProductNoByContainerAndSpec(ByVal strContainerName As String, ByVal strSpecID As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetProductNoByContainerAndSpec
        Dim strQuery As String = "SELECT c.childcontainer FROM workcenterdispatchingchildcont c" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = c.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN container cc on cc.containerid = wcd.containerid" & vbCrLf & _
                                "WHERE wcd.status <> 4 AND cc.containername = '" & strContainerName & "' AND wcd.specid = '" & strSpecID & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoByContainerAndSpec(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取派到生产单元的派工单已派工零件编号"
    Function GetProductNoByWorkCenterDispatch(ByVal strWCDName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetProductNoByWorkCenterDispatch
        Dim strQuery As String = "SELECT c.childcontainer FROM workcenterdispatchingchildcont c" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = c.workcenterdispatchingid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching cc on cc.workcenterdispatchingid = wcd.workcenterdispatchingid1" & vbCrLf & _
                                "WHERE cc.workcenterdispatchingname = '" & strWCDName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoByWorkCenterDispatch(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取派工单已报工的零件编号"
    Function GetReportedProductNoByDispatch(ByVal strWCDName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetReportedProductNoByDispatch
        Dim strQuery As String = "SELECT wc.childcontainer,w.workingreporthistoryid FROM workingreporthistorychildconta wc" & vbCrLf & _
                                "LEFT JOIN workingreporthistory w ON w.workingreporthistoryid = wc.workingreporthistoryid" & vbCrLf & _
                                "LEFT JOIN historymainline hml ON hml.historymainlineid = w.historymainlineid" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = w.workcenterdispatchingid" & vbCrLf & _
                                "WHERE hml.reversalstatus = 1 AND wcd.workcenterdispatchingname = '" & strWCDName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetReportedProductNoByDispatch(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取到小组的派工单对应的子派工单"
    Function GetDispatchingByParent(ByVal strParentName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetDispatchingByParent
        Dim strQuery As String = "SELECT wcd.workcenterdispatchingname FROM workcenterdispatching wcd" & vbCrLf & _
                                "LEFT JOIN workcenterdispatching wcd1 ON wcd1.workcenterdispatchingid = wcd.workcenterdispatchingid1" & vbCrLf & _
                                "WHERE wcd1.workcenterdispatchingname = '" & strParentName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetDispatchingByParent(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "根据设备获取人员列表"
    Function GetEmployeeListByResourceName(ByVal strResourceName As String, ByVal strWorkCenterName As String, ByVal strTeamName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetEmployeeListByResourceName
        'Dim strQuery As String = "SELECT e.employeeid,e.employeename,e.fullname" & vbCrLf & _
        '                        "FROM employee e" & vbCrLf & _
        '                        "LEFT JOIN resourcedef r ON r.resourceid = e.resourceid" & vbCrLf & _
        '                        "WHERE r.resourcename = '" & strResourceName & "'" & vbCrLf

        Dim strQuery As String = "SELECT e.employeeid,e.employeename,e.fullname" & vbCrLf & _
                                "FROM employee e" & vbCrLf & _
                                "LEFT JOIN employeeresourcelist erl ON erl.employeeid = e.employeeid" & vbCrLf & _
                                "LEFT JOIN resourcedef r ON r.resourceid = erl.resourcelistid" & vbCrLf

        If strWorkCenterName <> "" Then
            strQuery += "LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid" & vbCrLf & _
                        "LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid" & vbCrLf
        End If

        If strTeamName <> "" Then
            strQuery += "LEFT JOIN teamemployee te ON te.employeeid = e.employeeid" & vbCrLf & _
                        "LEFT JOIN team t ON t.teamid = te.teamid" & vbCrLf
        End If

        strQuery += "WHERE 1 = 1" & vbCrLf

        If strWorkCenterName <> "" Then
            strQuery += "AND wc.workcentername = '" & strWorkCenterName & "'" & vbCrLf
        End If

        If strTeamName <> "" Then
            strQuery += "AND t.teamname = '" & strTeamName & "'" & vbCrLf
        End If

        strQuery += "AND r.resourcename = '" & strResourceName & "'" & vbCrLf & _
                    "GROUP BY e.employeeid,e.employeename,e.fullname" & vbCrLf & _
                    "ORDER BY e.fullname DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetEmployeeListByResourceName(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取ERP生产任务列表"
    Function GetInventTaskList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetInventTaskList
        Dim strQuery As String = "SELECT it.createmodifieddate,it.departmentid,it.inventsizeid,it.inventtaskinfoid,it.inventtaskinfoname,it.itemid,it.itemname,it.publishtime," & vbCrLf & _
                                "it.journalstatus,it.planbegindate,it.planenddate,it.qty,it.taskcontent,it.taskstatus,it.tasktype,it.unitid,it.urgent,it.iscreatemfgorder,it.description " & vbCrLf & _
                                "FROM inventtaskinfo it" & vbCrLf & _
                                "WHERE 1 = 1 AND it.isread = 0" & vbCrLf

        If para.Keys.Contains("FactoryName") = True Then
            strQuery += "AND it.departmentid = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(it.itemid) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(it.itemname) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("MfgTaskNo") = True Then
            strQuery += "AND (LOWER(it.inventtaskinfoname) LIKE '%" & para("MfgTaskNo").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND it.createmodifieddate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND it.createmodifieddate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try

            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取质量记载单列表"
    Function GetQualityRecordList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetQualityRecordList
        Dim strQuery As String = "SELECT c.containername,pb.productname,p.description,p.productrevision,sb.specname,s.specrevision,qr.qty,pf.productfamilyname,pm.phasemarkname," & vbCrLf & _
                                "   e.employeename AS checkemployeename,e.fullname AS checkfullname,qr.checkdate,qr.recordstatus,qr.qualityrecordname," & vbCrLf & _
                                "   (CASE WHEN qr.recordstatus = 0 THEN '工人提交' WHEN qr.recordstatus = 1 THEN '检验提交' WHEN qr.recordstatus = 2 THEN '工艺已确认' WHEN qr.recordstatus = 5 THEN '工艺已退回' END) AS status," & vbCrLf & _
                                "   (CASE WHEN s.iskey = 1 THEN 'G' ELSE '' END) AS iskey," & vbCrLf & _
                                "   (CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold," & vbCrLf & _
                                "   e1.employeename AS toployeename,e1.fullname AS tofullname" & vbCrLf & _
                                "FROM qualityrecord qr" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = qr.containerid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = qr.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = qr.checkemployeeid" & vbCrLf & _
                                "LEFT JOIN employee e1 ON e1.employeeid = qr.technologistemployeeid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cu.factoryid" & vbCrLf & _
                                "WHERE qr.recordstatus IN (" & vbCrLf

        If para.Keys.Contains("IsWorkerQRToTech") = True Then
            strQuery += "0,"
        End If
        strQuery += "1,2)" & vbCrLf


        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
        End If
        'If para.Keys.Contains("SecretLevel") = True Then
        '    strQuery += "AND (c.secretlevel <= " & para("SecretLevel").ToString & " OR c.secretlevel IS NULL)" & vbCrLf
        'End If
        If para.Keys.Contains("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("WorkCenterName") = True Then
            strQuery += "AND wc.workcentername = '" & para("WorkCenterName").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("SpecName") = True Then
            strQuery += "AND (LOWER(sb.specname) LIKE '%" & para("MfgTaskNo").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND qr.checkdate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND qr.checkdate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try

            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取质量记载单的零件编号列表"
    Function GetProductNoListByQualityRecord(ByVal strName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetProductNoListByQualityRecord
        Dim strQuery As String = "SELECT q.qualityrecordname,pi.productno,pi.qualityrecordnotes,qd.qualitydisposeadvicename,pi.qualitydisposenotes," & vbCrLf & _
                                "pi.containerlist AS speclist,pi.qty,replace(pi.productno,c.containername || '-') AS productnodisplay" & vbCrLf & _
                                "FROM productnoinfo pi" & vbCrLf & _
                                "LEFT JOIN qualityrecordproductnoinfo qi ON qi.productnoinfoid = pi.productnoinfoid" & vbCrLf & _
                                "LEFT JOIN qualityrecord q ON q.qualityrecordid = qi.qualityrecordid" & vbCrLf & _
                                "LEFT JOIN qualitydisposeadvice qd ON qd.qualitydisposeadviceid = pi.qualitydisposeadviceid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = q.containerid" & vbCrLf & _
                                "WHERE q.qualityrecordname = '" & strName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoListByQualityRecord(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取跟踪卡基本信息"
    Function GetContainerInfo(ByVal strContainerName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetContainerInfo
        Dim strQuery As String = GetContainerInfoSQL()
        strQuery += "AND c.containername = '" & strContainerName & "'" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取跟踪卡对应的生产订单工艺确认信息"
    Function GetContainerTechConfirmInfo(ByVal strContainerID As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetContainerTechConfirmInfo
        Dim strQuery As String = "SELECT c.containerid,c.containername,mo.mfgordername,mo.techconfirm,mo.techconfirmdate," & vbCrLf & _
                                "   e.employeeid,e.employeename,e.fullname" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid = mo.techconfirmemployeeid" & vbCrLf & _
                                "WHERE c.containerid = '" & strContainerID & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerTechConfirmInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取工序信息"
    Function GetSpecInfo(ByVal strSpecID As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetSpecInfo
        Dim strQuery As String = "SELECT sb.specname,s.specrevision,s.specid,o.operationname" & vbCrLf & _
                                "FROM spec s" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN operation o ON o.operationid = s.operationid" & vbCrLf & _
                                "WHERE s.specid = '" & strSpecID & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetSpecInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取已经通过 工段长接收 节点的批次"
    Function GetContainerHasMaterial(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetContainerHasMaterial
        Dim strQuery As String = "SELECT distinct mo.mfgordername,c.containername,pf.productfamilyname,pm.phasemarkname,pb.productname,p.description," & vbCrLf & _
                                "   c.originalqty,c.qty,pc.prioritycodename,c.plannedstartdate,c.plannedcompletiondate,sb.specname,c.containerid,p.productid" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "LEFT JOIN phasemark pm ON pm.phasemarkid = p.phasemarkid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = cu.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = c.originalfactoryid" & vbCrLf & _
                                "LEFT JOIN (" & vbCrLf & _
                                "   SELECT hml.containerid" & vbCrLf & _
                                "   FROM movehistory mh" & vbCrLf & _
                                "   LEFT JOIN historymainline hml ON hml.historymainlineid = mh.historymainlineid" & vbCrLf & _
                                "   LEFT JOIN workflowstep wfs ON wfs.workflowstepid = hml.workflowstepid" & vbCrLf & _
                                "   LEFT JOIN cdodefinition cdo ON cdo.cdodefid = hml.txntype" & vbCrLf & _
                                "   WHERE hml.reversalstatus = 1 AND cdo.cdoname = 'MoveStd'" & vbCrLf & _
                                "   AND (wfs.workflowstepname = '工段长接收' or wfs.workflowstepname = '领料')" & vbCrLf & _
                                ") h ON h.containerid = c.containerid" & vbCrLf & _
                                "WHERE c.parentcontainerid IS NULL AND c.containerid <> c.containername" & vbCrLf & _
                                "AND h.containerid IS NOT NULL" & vbCrLf

        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If

        strQuery += "ORDER BY c.containername ASC" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取跟踪卡未开合格证的产品序号"
    Function GetProductNoByContainerName(ByVal strContainerName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetProductNoByContainerName
        Dim strQuery As String = "SELECT REPLACE(c.containername,'" & strContainerName & "' || '-','') AS ContainerName" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN container cc ON cc.containerid = c.parentcontainerid" & vbCrLf & _
                                "WHERE cc.containername = '" & strContainerName & "'AND c.status = 1 AND c.qty > 0" & vbCrLf & _
                                "AND" & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT q.qualificationname FROM qualification q" & vbCrLf & _
                                "   WHERE ',' || q.productnoinfo || ',' LIKE '%,' || REPLACE(c.containername,'" & strContainerName & "' || '-','') || ',%'" & vbCrLf & _
                                ") IS NULL" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductNoByContainerName(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取跟踪卡基本信息"
    Function GetContainerInfoByID(ByVal strContainerID As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetContainerInfoByID
        Dim strQuery As String = "SELECT c.containername,pb.productname,p.description,c.originalqty,c.qty,c.containerid," & vbCrLf & _
                                "   sb.specname,c.plannedstartdate,c.plannedcompletiondate,cpn.containerproductnoname," & vbCrLf & _
                                "   wfs.workflowstepname,wf.workflowid,wf.workflowrevision,wfb.workflowname,cdo.cdoname" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN currentstatus cs ON cs.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = cs.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cs.workflowstepid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid" & vbCrLf & _
                                "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = wf.cdotypeid" & vbCrLf & _
                                "LEFT JOIN containerproductno cpn ON cpn.containerproductnoname = c.containername" & vbCrLf & _
                                "WHERE c.containerid = '" & strContainerID & "'" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取批次当前工序以后的工序列表"
    Function GetLeftSpecListByContainer(ByVal strContainerName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetLeftSpecListByContainer
        Dim strQuery As String = "SELECT wstep.*,sb.specname,s.specrevision" & vbCrLf & _
                                "FROM spec s" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "RIGHT JOIN" & vbCrLf & _
                                "(" & vbCrLf & _
                                "   SELECT wfs.workflowstepname,wfs.specbaseid,wfs.specid AS specid1,wfs.sequence," & vbCrLf & _
                                "       (CASE WHEN wfs.specid = '0000000000000000' THEN (SELECT revofrcdid FROM specbase WHERE specbaseid = wfs.specbaseid) ELSE wfs.specid END) AS specid" & vbCrLf & _
                                "   FROM workflowstep wfs" & vbCrLf & _
                                "   RIGHT JOIN" & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT wfs1.sequence,wfs1.workflowid FROM workflowstep wfs1" & vbCrLf & _
                                "       LEFT JOIN currentstatus cu ON cu.workflowstepid = wfs1.workflowstepid" & vbCrLf & _
                                "       LEFT JOIN container c ON c.currentstatusid = cu.currentstatusid" & vbCrLf & _
                                "       WHERE c.containername = '" & strContainerName & "'" & vbCrLf & _
                                "   ) wf ON wfs.workflowid = wf.workflowid" & vbCrLf & _
                                "   WHERE wfs.sequence > wf.sequence" & vbCrLf & _
                                ") wstep ON wstep.specid = s.specid" & vbCrLf & _
                                "ORDER BY wstep.sequence"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetLeftSpecListByContainer(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "创建批次"
    Function StartContainer(ByVal para As ContainerStartInfo) As uMESProcessResult Implements uMES_bjDispatchIBusiness.StartContainer
        Dim result As New uMESProcessResult
        Try
            Dim strWorkflowName As String = para.WorkflowName
            Dim strContainerName As String = para.ContainerName
            Dim boolUseROR As String = para.WorkflowuseROR

            '创建批次
            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ContainerName"
            dataEntity.ClientDataValue = para.ContainerName
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Level"
            dataEntity.ClientDataValue = para.Level
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            Dim strProdName As String = para.ProductName
            Dim strProdRev As String = para.ProductRevision

            dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Product"
            dataEntity.ClientDataValue = strProdName
            dataEntity.ClientDataVersion = strProdRev
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Owner"
            dataEntity.ClientDataValue = para.Owner
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "Qty"
            dataEntity.ClientDataValue = para.Qty
            m_DataList.Add(dataEntity)

            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            'dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            'dataEntity.ClientDataName = "IsSelfMfg"
            'dataEntity.ClientDataValue = para.IsSelfMfg
            'm_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "UOM"
            dataEntity.ClientDataValue = para.UOM
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "StartReason"
            dataEntity.ClientDataValue = para.StartReason
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.CurrentStatusDetails
            dataEntity.ClientDataName = "Workflow"
            dataEntity.ClientDataValue = para.MfgWorkflowName
            dataEntity.ClientDataVersion = para.MfgWorkflowRevision
            dataEntity.ClientDataIsROR = para.MfgWorkflowuseROR

            'If para.IsNewWorkflowRev = True Then
            '    dataEntity.ClientDataVersion = para.ContainerName
            'Else
            '    dataEntity.ClientDataVersion = para.WorkflowRevision
            'End If

            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.CurrentStatusDetails
            dataEntity.ClientDataName = "Factory"
            dataEntity.ClientDataValue = para.Factory
            m_DataList.Add(dataEntity)

            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.subentityField
            'dataEntity.ClientInputTypeEnum = InputTypeEnum.CurrentStatusDetails
            'dataEntity.ClientDataName = "Location"
            'dataEntity.ClientDataValue = para.Location
            'm_DataList.Add(dataEntity)

            If para.MfgOrder IsNot Nothing Then
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
                dataEntity.ClientDataName = "MfgOrder"
                dataEntity.ClientDataValue = para.MfgOrder
                m_DataList.Add(dataEntity)
            End If


            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            'dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            'dataEntity.ClientDataName = "WorkCenter"
            'dataEntity.ClientDataValue = para.WorkCenter
            'm_DataList.Add(dataEntity)

            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            'dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            'dataEntity.ClientDataName = "PriorityCode"
            'dataEntity.ClientDataValue = para.Priority
            'm_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "PlannedStartDate"
            dataEntity.ClientDataValue = para.PlannedStartDate
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "PlannedCompletionDate"
            dataEntity.ClientDataValue = para.PlannedCompletionDate
            m_DataList.Add(dataEntity)

            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            'dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            'dataEntity.ClientDataName = "MaterialRequirementDate"
            'dataEntity.ClientDataValue = para.MaterialRequirementDate
            'm_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "StartEmployee"
            dataEntity.ClientDataValue = para.StartEmployee
            m_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ContainerType"
            dataEntity.ClientDataValue = para.ContainerType
            m_DataList.Add(dataEntity)

            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            'dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            'dataEntity.ClientDataName = "TogetherFlag"
            'dataEntity.ClientDataValue = para.TogetherFlag
            'm_DataList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ContainerComment"
            dataEntity.ClientDataValue = para.Comment
            m_DataList.Add(dataEntity)

            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            'dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            'dataEntity.ClientDataName = "SecretLevel"
            'dataEntity.ClientDataValue = para.SecretLevel
            'm_DataList.Add(dataEntity)

            'dataEntity = New ClientAPIEntity
            'dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
            'dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            'dataEntity.ClientDataName = "MfgWorkflow"
            'dataEntity.ClientDataValue = para.MfgWorkflowName
            'dataEntity.ClientDataVersion = para.MfgWorkflowRevision
            'm_DataList.Add(dataEntity)

            Dim m_ChildList As New Dictionary(Of String, List(Of ClientAPIEntity))

            Dim dtChildList As DataTable = para.ChildList

            If dtChildList IsNot Nothing Then
                If dtChildList.Rows.Count > 0 Then
                    For i As Integer = 0 To dtChildList.Rows.Count - 1
                        Dim m_Child As New List(Of ClientAPIEntity)
                        Dim stepEntity As New ClientAPIEntity

                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        stepEntity.ClientDataName = "ContainerName"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("ContainerName").ToString
                        m_Child.Add(stepEntity)

                        stepEntity = New ClientAPIEntity
                        stepEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                        stepEntity.ClientDataName = "Level"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("Level").ToString
                        m_Child.Add(stepEntity)

                        stepEntity = New ClientAPIEntity
                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        stepEntity.ClientDataName = "Qty"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("Qty").ToString
                        m_Child.Add(stepEntity)

                        stepEntity = New ClientAPIEntity
                        stepEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                        stepEntity.ClientDataName = "ContainerType"
                        stepEntity.ClientDataValue = dtChildList.Rows(i).Item("ContainerType").ToString
                        m_Child.Add(stepEntity)

                        m_ChildList.Add(i.ToString, m_Child)
                    Next
                End If
            End If

            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = para.ServerHostInfo
            Dim serverHost As String = daServerHost.ServerHost
            Dim serverPort As String = daServerHost.ServerPort
            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            '创建批次结束

            Try
                Dim api As New CamstarClientAPI(serverUser, serverPassword)
                result.Result = api.Start(m_DataList, m_ChildList, strInfo)
                result.Message = strInfo
            Catch ex As Exception
                result.Result = False
                result.Message = ex.Message
            End Try

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "Split"
    Function SplitContainer(ByVal para As ContainerSplitInfo) As uMESProcessResult Implements uMES_bjDispatchIBusiness.SplitContainer
        Dim result As New uMESProcessResult
        Try
            Dim strContainerName As String = para.ContainerName

            '拆分批次
            Dim m_DataList As New List(Of ClientAPIEntity)

            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = strContainerName
            dataEntity.ClientOtherValue = "Lot"
            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            m_DataList.Add(dataEntity)

            Dim m_ChildList As New Dictionary(Of String, List(Of ClientAPIEntity))

            Dim ToInfoList As List(Of ToContainerInfo) = para.ToContainerInfo

            Dim m_Child As New List(Of ClientAPIEntity)
            For i As Integer = 0 To ToInfoList.Count - 1
                Dim childEntity As New ClientAPIEntity
                childEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                childEntity.ClientDataName = "ToContainerName"
                childEntity.ClientDataValue = ToInfoList(i).ToContainerName
                m_Child.Add(childEntity)

                childEntity = New ClientAPIEntity
                childEntity.ClientDataTypeEnum = DataTypeEnum.DataField
                childEntity.ClientDataName = "Qty"
                childEntity.ClientDataValue = ToInfoList(i).Qty
                m_Child.Add(childEntity)

                m_ChildList.Add(i.ToString, m_Child)
            Next

            Dim dtChildList As DataTable = para.ChildContainer

            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = para.ServerHostInfo
            Dim serverHost As String = daServerHost.ServerHost
            Dim serverPort As String = daServerHost.ServerPort
            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Try
                Dim api As New CamstarClientAPI(serverUser, serverPassword)
                result.Result = api.Split(m_DataList, m_ChildList, dtChildList, strInfo)
                result.Message = strInfo
            Catch ex As Exception
                result.Result = False
                result.Message = ex.Message
            End Try

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "工艺确认生产订单"
    Function TechConfirmMfgOrder(ByVal para As MfgOrderInfo, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMES_bjDispatchIBusiness.TechConfirmMfgOrder
        Dim dataEntityList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "TechConfirm"
        dataEntity.ClientDataValue = para.TechConfirm
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "TechConfirmDate"
        dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "TechConfirmEmployee"
        dataEntity.ClientDataValue = para.TechConfirmEmployee
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Notes"
        dataEntity.ClientDataValue = para.Notes
        dataEntityList.Add(dataEntity)

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.UpdateModelByStandard("MfgOrderMaintDoc", "MfgOrderMaint", para.Name, "", dataEntityList, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "创建生产订单"
    Function CreateMfgOrder(ByVal para As MfgOrderInfo, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMES_bjDispatchIBusiness.CreateMfgOrder
        Dim dataEntityList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = para.Name
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "MfgTaskNo"
        dataEntity.ClientDataValue = para.MfgTaskNo
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientDataName = "Product"
        dataEntity.ClientDataValue = para.ProductName
        dataEntity.ClientDataVersion = para.ProductRevision
        dataEntity.ClientDataIsROR = para.ProductuseROR
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Qty"
        dataEntity.ClientDataValue = para.Qty
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "UOM"
        dataEntity.ClientDataValue = para.UOM
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "Priority"
        dataEntity.ClientDataValue = para.Priority
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "ReportingFactory"
        dataEntity.ClientDataValue = para.Factory
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedStartDate"
        dataEntity.ClientDataValue = para.PlannedStartDate
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedCompletionDate"
        dataEntity.ClientDataValue = para.PlannedCompletionDate
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "MfgBatch"
        dataEntity.ClientDataValue = para.MfgBatch
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "InventTaskInfo"
        dataEntity.ClientDataValue = para.InventTaskInfoName
        dataEntityList.Add(dataEntity)

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.CreateModelByStandard("MfgOrderMaintDoc", "MfgOrderMaint", "New", dataEntityList, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "修改生产订单"
    Function UpdateMfgOrder(ByVal para As MfgOrderInfo, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMES_bjDispatchIBusiness.UpdateMfgOrder
        Dim dataEntityList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "MfgTaskNo"
        dataEntity.ClientDataValue = para.MfgTaskNo
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
        dataEntity.ClientDataName = "Product"
        dataEntity.ClientDataValue = para.ProductName
        dataEntity.ClientDataVersion = para.ProductRevision
        dataEntity.ClientDataIsROR = para.ProductuseROR
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "Qty"
        dataEntity.ClientDataValue = para.Qty
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "UOM"
        dataEntity.ClientDataValue = para.UOM
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "Priority"
        dataEntity.ClientDataValue = para.Priority
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "ReportingFactory"
        dataEntity.ClientDataValue = para.Factory
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedStartDate"
        dataEntity.ClientDataValue = para.PlannedStartDate
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "PlannedCompletionDate"
        dataEntity.ClientDataValue = para.PlannedCompletionDate
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "MfgBatch"
        dataEntity.ClientDataValue = para.MfgBatch
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        dataEntity.ClientDataName = "InventTaskInfo"
        dataEntity.ClientDataValue = para.InventTaskInfoName
        dataEntityList.Add(dataEntity)

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.UpdateModelByStandard("MfgOrderMaintDoc", "MfgOrderMaint", para.Name, "", dataEntityList, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "修改生产任务"
    Function UpdateInventTaskInfo(ByVal para As InventTaskInfo, ByVal serverInfo As ServerHostInfo) As uMESProcessResult Implements uMES_bjDispatchIBusiness.UpdateInventTaskInfo
        Dim dataEntityList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "IsCreateMfgOrder"
        dataEntity.ClientDataValue = para.IsCreateMfgOrder
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntity.ClientDataName = "IsRead"
        dataEntity.ClientDataValue = para.IsRead
        dataEntityList.Add(dataEntity)

        Dim result As New uMESProcessResult
        Dim strInfo As String = ""

        Try
            Dim api As New CamstarClientAPI(serverInfo.UserName, serverInfo.Password)
            result.Result = api.UpdateModelByStandard("InventTaskInfoMaintDoc", "InventTaskInfoMaint", para.Name, "", dataEntityList, strInfo)
            result.Message = strInfo
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "添加或更新批次零件编号信息"
    Function AddAndUpdateContainerProductNo(ByVal p_UpdateName As String, ByVal p_perform As String, ByVal p_dataEntityList As List(Of ClientAPIEntity), _
                                      ByVal p_dataProductList As Dictionary(Of String, List(Of ClientAPIEntity))) As uMESProcessResult Implements uMES_bjDispatchIBusiness.AddAndUpdateContainerProductNo
        Dim result As New uMESProcessResult
        result.Result = False
        Return result
    End Function
#End Region

#Region "BJ获取工段派工跟踪卡列表"
    Public Function BJ_GetFactoryDispatchContainerList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.BJ_GetFactoryDispatchContainerList

        Dim strQuery As String = "select m.mfgordername,cs.specid ,c.containername,c.containerid ,c.qty ,c.plannedstartdate,c.holdreasonid,c.plannedcompletiondate,pb.productname,p.processno,p.description,f.factoryname,f.factoryid," & vbCrLf & _
                                 "f.description as factoryfullname, s.specrevision, sb.specname，cc.dqty,cc1.reportqty,s.iscooperation " & vbCrLf & _
                                 "from container c " & vbCrLf & _
                                 "left join currentstatus cs on cs.currentstatusid=c.currentstatusid " & vbCrLf & _
                                 "left join product p on p.productid=c.productid " & vbCrLf & _
                                 "left join productbase pb  on pb.productbaseid=p.productbaseid " & vbCrLf & _
                                 "left join factory f on f.factoryid=cs.factoryid " & vbCrLf & _
                                 "left join workflowstep wt on wt.workflowstepid=cs.workflowstepid " & vbCrLf & _
                                 "inner join spec s on s.specid=(CASE WHEN wt.specid = '0000000000000000' THEN (SELECT sb1.revofrcdid FROM specbase sb1 WHERE sb1.specbaseid = wt.specbaseid) ELSE wt.specid END) " & vbCrLf & _
                                 "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
                                 "left join mfgorder m on m.mfgorderid=c.mfgorderid " & vbCrLf & _
                                 "left join (select sum(wcd.qty) as dqty,wcd.containerid,wcd.specid from workcenterdispatching wcd  group by wcd.specid,wcd.containerid) cc on cc.containerid=c.containerid and cc.specid=cs.specid " & vbCrLf & _
                                 "left join (select sum(wcd.reportedqty) as reportqty,wcd.containerid,wcd.specid from workcenterdispatching wcd  group by wcd.specid,wcd.containerid) cc1 on cc1.containerid=c.containerid and cc1.specid=cs.specid " & vbCrLf &
                                 "where c.status=1 and c.parentcontainerid is null and c.containerid<>c.containername and c.holdreasonid is null  "

        If para.Keys.Contains("MfgOrderName") = True Then
            strQuery += "AND LOWER(m.mfgordername) LIKE '%" & para("MfgOrderName").ToString.ToLower & "%' " & vbCrLf
        End If

        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) LIKE '%" & para("ContainerName").ToString.ToLower & "%' " & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%') " & vbCrLf
        End If

        If para.Keys.Contains("processno") = True Then
            strQuery += "AND (LOWER(p.processno) LIKE '%" & para("processno").ToString.ToLower & "%' OR LOWER(p.processno) LIKE '%" & para("processno").ToString.ToLower & "%') " & vbCrLf
        End If

        strQuery += " ORDER BY pb.productname,c.containername ASC" & vbCrLf

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery

        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            Dim DT As DataTable = r.DBTable.Copy
            'DT = DataAdjust.FormatDataSetSpecNameByColumNameBJ("specname", DT).Copy()
            'DT = DataAdjust.FormatDataContainerNameByColumNameBJ("containername", DT)


            Dim strContainerList As String = ""
            For i = 0 To DT.Rows.Count - 1
                strContainerList = strContainerList & DT.Rows(i).Item("containername") & "','"
            Next

            If strContainerList.Length > 0 Then
                strContainerList = strContainerList.Substring(0, strContainerList.Length - 3)
            End If

            strQuery = "select c.containername, wt.workflowstepname,wt.sequence,wt.workflowid,sb2.specname,wch.* from workcenterdispatching wch " & vbCrLf & _
                       "left join workflowstep wt on  wch.specid=(case when wt.specid='0000000000000000' Then (select sb.revofrcdid from specbase sb where sb.specbaseid=wt.specbaseid) else wt.specid end) " & vbCrLf & _
                       "left join container c on c.containerid=wch.containerid " & vbCrLf & _
                       "left join spec s on s.specid=wch.specid " & vbCrLf & _
                       "left join specbase sb2 on sb2.specbaseid=s.specbaseid " & vbCrLf & _
                       "where wch.dispatchtype=1 and wt.workflowid in ( " & vbCrLf & _
                       "select wt1.workflowid from workflowstep wt1 " & vbCrLf & _
                       "left join currentstatus cs on cs.workflowstepid=wt1.workflowstepid " & vbCrLf & _
                       "left join container c on c.currentstatusid=cs.currentstatusid " & vbCrLf & _
                       "where c.containername in （'" & strContainerList & "')）" & vbCrLf & _
                       "and  c.containername in ('" & strContainerList & "')"

            Dim workSetpTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strQuery)

            DT.Columns.Add("LastAtchSpecName")

            For Each row As DataRow In DT.Rows
                Dim rowtmps As DataRow() = workSetpTable.Select("containername='" & row("containername").ToString().Trim() & "'", "sequence")
                If rowtmps.Length > 0 Then

                    Dim strArray As String() = rowtmps(rowtmps.Length - 1)("specname").ToString().Split("-")
                    If strArray.Length >= 2 Then
                        row("LastAtchSpecName") = strArray(strArray.Length - 2) & "-" & strArray(strArray.Length - 1)
                    Else
                        row("LastAtchSpecName") = rowtmps(rowtmps.Length - 1)("specname")
                    End If
                End If
            Next

            result.Result = True
            result.DBTable = DT
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "BJ获得派工批次工序列表"
    Public Function BJ_GetDispatchContainerSpecList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjDispatchIBusiness.BJ_GetDispatchContainerSpecList

        Dim strSql = "select wt.workflowstepid,wt.workflowstepname,wt.specbaseid,wt.specid,wt.workflowid,wt.sequence,sb.specname,sb1.specname as specname1,sb1.revofrcdid as specid1," & vbCrLf
        strSql += "s.factoryid,s1.factoryid as factoryid1,s.specrevision,s1.specrevision as specrevision1 from workflowstep wt " & vbCrLf
        strSql += "left join workflow w on w.workflowid=wt.workflowid " & vbCrLf
        strSql += "left join spec s on s.specid=wt.specid " & vbCrLf
        strSql += "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf
        strSql += "left join specbase sb1 on sb1.specbaseid=wt.specbaseid " & vbCrLf
        strSql += "left join spec s1 on s1.specid=sb1.revofrcdid " & vbCrLf
        strSql += "where  w.workflowid=( " & vbCrLf
        strSql += "select wt1.workflowid from workflowstep wt1 " & vbCrLf
        strSql += "left join currentstatus cs on cs.workflowstepid=wt1.workflowstepid " & vbCrLf
        strSql += "left join container c on c.currentstatusid=cs.currentstatusid " & vbCrLf
        strSql += "where (s.iscooperation is null and s1.iscooperation is null) and  c.containername='" & para("ContainerName").Trim() & "')  order by wt.sequence"

        Dim stepTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Dim stepTmpTable As DataTable = stepTable.Clone()

        Dim strCheckTmp As String = ConfigurationSettings.AppSettings("CheckSpecKey")
        Dim strKeys As String() = strCheckTmp.Split(",")

        For Each row As DataRow In stepTable.Rows
            If row("specid").ToString() = "0000000000000000" Then
                row("specid") = row("specid1")
            End If
            If row.IsNull("specname") = True Then
                row("specname") = row("specname1")
            End If
            If row.IsNull("specrevision") = True Then
                row("specrevision") = row("specrevision1")
            End If
            If row("factoryid").ToString().Trim() = para("factoryid").Trim Or row("factoryid1").ToString().Trim() = para("factoryid").Trim Then
                Dim intOK As Integer = 0
                For i As Integer = 0 To strKeys.Length - 1
                    If row("specname").ToString().IndexOf(strKeys(i)) <> -1 Then
                        intOK = 1
                        Exit For
                    End If
                Next
                If intOK = 0 Then
                    stepTmpTable.ImportRow(row)
                End If
            End If
        Next

        strSql = "select m.qty1,m.specid,m.containerid from  (select sum(wcd.qty) as qty1,wcd.specid,wcd.containerid from workcenterdispatching wcd " & vbCrLf
        strSql += "left join container c on c.containerid=wcd.containerid " & vbCrLf
        strSql += "where c.containername='" & para("ContainerName").Trim() & "' group by wcd.specid,wcd.containerid) m  " & vbCrLf
        strSql += "inner join container c1 on c1.containerid=m.containerid and m.qty1>=c1.qty"

        Dim dispatchingTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        '去掉已经外协提请的工序
        strSql = "select c.containername,t.containerid,ts.specid  from SynergicSpecValidate t " & vbCrLf
        strSql += "left join  SynergicSpecInfo ts on ts.synergicspecvalidateid=t.synergicspecvalidateid " & vbCrLf
        strSql += "left join container c on c.containerid=t.containerid  where c.containername='" & para("ContainerName").Trim() & "'"

        Dim syncTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        Dim returnStepTable = stepTable.Clone()

        For Each row As DataRow In stepTmpTable.Rows
            Dim strFilter As String = String.Format("specid='{0}'", row("specid").ToString().Trim())
            Dim rowTmps As DataRow() = dispatchingTable.Select(strFilter)
            If rowTmps.Length = 0 Then
                If row("specname").ToString().Trim().ToString().IndexOf("领料") <> -1 Or
                   row("specname").ToString().Trim().ToString().IndexOf("准备") <> -1 Or
                   row("specname").ToString().Trim().ToString().IndexOf("下料") <> -1 Then
                    Continue For
                End If
                Dim syncRows As DataRow() = syncTable.Select(strFilter)
                If syncRows.Length > 0 Then
                    Continue For
                End If
                returnStepTable.ImportRow(row)
            End If
        Next
        'returnStepTable = DataAdjust.FormatDataSetSpecNameByColumNameBJ("specname", returnStepTable).Copy()
        Return returnStepTable
    End Function
#End Region

#Region "BJ获得派工批次工序列表"
    Public Function BJ_GetSpecDispatchQtyList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjDispatchIBusiness.BJ_GetSpecDispatchQtyList
        Dim sb_1 As String = "select c.containername,sb.specname,nvl(wfs1.workflowstepname,wfs.workflowstepname) workflowstepname,sum(wcd.qty) as qty from workcenterdispatching wcd  " & vbCrLf & _
              " left join spec s on s.specid=wcd.specid " & vbCrLf & _
              "  left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
              " left join container c on c.containerid=wcd.containerid " & vbCrLf & _
              "       left join workflowstep wfs1 on wfs1.specid=(case when s.specid='0000000000000000' Then wfs1.specid else s.specid end) " & vbCrLf & _
                "    left join workflowstep wfs on wfs.specbaseid=(case when s.specbaseid='0000000000000000' Then wfs.specbaseid else s.specbaseid end) " & vbCrLf & _
              " where c.containername='" & para("containername").ToString() & "' and nvl(wfs1.workflowstepname,wfs.workflowstepname)='" & para("DisplaySpecName").ToString() & "' " & vbCrLf
        sb_1 += "  group by c.containername,sb.specname,wfs.workflowstepname,nvl(wfs1.workflowstepname,wfs.workflowstepname) "
        Dim resultdt As DataTable = dal.StandardGetDataTable(sb_1)
        Return resultdt
    End Function
#End Region

#Region "BJ获得Workflowstepname对应的Specname"
    Public Function BJ_GetSpecNameList(ByVal para As String) As DataTable Implements uMES_bjDispatchIBusiness.BJ_GetSpecNameList
        Dim s As String = "select  s.specrevision,sb.specname,nvl(wfs1.workflowstepname,wfs.workflowstepname) as workflowstepname from spec s" & vbCrLf & _
         "  left join specbase sb on sb.specbaseid=s.specbaseid" & vbCrLf & _
          "  left join workflowstep wfs1 on wfs1.specid=(case when s.specid='0000000000000000' Then wfs1.specid else s.specid end)" & vbCrLf & _
          "left join workflowstep wfs on wfs.specbaseid=(case when s.specbaseid='0000000000000000' Then wfs.specbaseid else s.specbaseid end)" & vbCrLf & _
           " where nvl(wfs1.workflowstepname,wfs.workflowstepname)='" & para & "'"
        Dim resultdt As DataTable = dal.StandardGetDataTable(s)
        Return resultdt
    End Function
#End Region

#Region "BJ获得资源所有的任务"
    Public Function BJ_GetResourceTasks(ByVal para As String) As DataTable Implements uMES_bjDispatchIBusiness.BJ_GetResourceTasks

        Dim sb As String = "select rd.resourceename,c.containername,pb.productname,sb.specname, p.description," & vbCrLf & _
                           "wcd.qty,wcd.plannedcompletiondate from resourcedef rd " & vbCrLf & _
                           "left join workcenterdispatching wcd on wcd.resourceid=rd.resourceid " & vbCrLf & _
                           "left join container c on c.containerid=wcd.containerid " & vbCrLf & _
                           "left join product p on p.productid=c.productid " & vbCrLf & _
                           "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf & _
                           "left join spec s on s.specid=wcd.specid " & vbCrLf & _
                           "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
                           "where  c.status = 1 and wcd.status<>3 and  c.containername <> c.containerid AND c.parentcontainerid IS NULL and  rd.resourcename='" & para & "' order by pb.productname,c.containername" & vbCrLf
        Dim resultdt As DataTable = dal.StandardGetDataTable(sb)
        'resultdt = DataAdjust.FormatDataSetSpecNameByColumNameBJ("specname", resultdt)
        'resultdt = DataAdjust.FormatDataContainerNameByColumNameBJ("containername", resultdt)

        Return resultdt
    End Function
#End Region

#Region "BJ获得资源绑定的人员信息"
    Public Function BJ_GetResourceMans(ByVal para As String) As DataTable Implements uMES_bjDispatchIBusiness.BJ_GetResourceMans
        Dim sb_1 As String = "select e.employeename,e.fullname from employeeresourcelist erl " & vbCrLf & _
            " left join resourcedef rd on rd.resourceid=erl.resourcelistid" & vbCrLf & _
                  "      left join employee e on e.employeeid=erl.employeeid" & vbCrLf & _
            "  where rd.resourcename='" & para & "'" & vbCrLf

        Dim resultdt As DataTable = dal.StandardGetDataTable(sb_1)
        Return resultdt
    End Function
#End Region

#Region "BJ获得班组绑定的人员信息"
    Public Function BJ_GetTeamMans(ByVal para As String) As DataTable Implements uMES_bjDispatchIBusiness.BJ_GetTeamMans
        Dim sb_1 As String = "select e.employeename,e.fullname,t.teamname from  teamemployee te " & vbCrLf & _
                             "left join team t on t.teamid=te.teamid " & vbCrLf & _
                             "left join employee e on e.employeeid=te.employeeid " & vbCrLf & _
                             "where t.teamname='" & para & "'"
        Dim resultdt As DataTable = dal.StandardGetDataTable(sb_1)
        Return resultdt
    End Function
#End Region

#Region "BJ获得班组的所有任务"
    Public Function BJ_GetTeamTasks(ByVal para As String) As DataTable Implements uMES_bjDispatchIBusiness.BJ_GetTeamTasks

        Dim sb As String = "select t.teamname,t.teamid,c.containername,pb.productname,p.processno,sb.specname, p.description," & vbCrLf & _
                           "wcd.qty,wcd.plannedcompletiondate from team t " & vbCrLf & _
                           "left join workcenterdispatching wcd on wcd.teamid=t.teamid" & vbCrLf & _
                           "left join container c on c.containerid=wcd.containerid " & vbCrLf & _
                           "left join product p on p.productid=c.productid " & vbCrLf & _
                           "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf & _
                           "left join spec s on s.specid=wcd.specid " & vbCrLf & _
                           "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
                           "where  c.status = 1 and wcd.status<>3  AND c.containername <> c.containerid AND c.parentcontainerid IS NULL and  t.teamname='" & para & "' order by p.processno,pb.productname,c.containername" & vbCrLf
        Dim resultdt As DataTable = dal.StandardGetDataTable(sb)
        'resultdt = DataAdjust.FormatDataSetSpecNameByColumNameBJ("specname", resultdt)
        'resultdt = DataAdjust.FormatDataContainerNameByColumNameBJ("containername", resultdt)
        Return resultdt
    End Function
#End Region

#Region "BJ获得工序的派工数量"
    Public Function BJ_SpecQty(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjDispatchIBusiness.BJ_SpecQty

        Dim strSql = "select c.containername,sb.specname,nvl(sum(wcd.qty),0)  as dqty,nvl(sum(wcd.reportedqty),0) as reportqty from workcenterdispatching wcd " & vbCrLf & _
                     "left join spec s on s.specid=wcd.specid  " & vbCrLf & _
                     "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
                     "left join container c on c.containerid=wcd.containerid " & vbCrLf & _
                     "where c.containername='" & para("containername") & "' and sb.specname = '" & para("SpecName") & "' group by c.containername,sb.specname "

        Dim resultdt As DataTable = dal.StandardGetDataTable(strSql)
        Return resultdt
    End Function
#End Region

#Region "BJ获得派工单信息"
    Public Function BJ_DispatchInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.BJ_DispatchInfo

        Dim strQuery As String = "select m.mfgordername, p.processno, p.productid,p.productrevision,pb.productname,sb.specname,s.specrevision,p.description," & vbCrLf
        strQuery += "c.containername, r.resourceename, r.ResourceName, te.teamname, f.factoryname,f.description as mfactoryName," & vbCrLf
        strQuery += "DECODE(t.status,'0','已派工','1','已接收','2','已报工','3','已完成','4','已关闭') AS status,t.workcenterdispatchingid,t.workcenterdispatchingname, " & vbCrLf
        strQuery += "(CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold,t.isartworksheet,t.isdevice,t.isgongzhuang,t.isncprogram,t.issupplementmaterial,t.istool," & vbCrLf
        strQuery += " (case when s1.specid=t.specid or sb2.revofrcdid=t.specid then 1 else 0 end) as reportState," & vbCrLf
        strQuery += "t.containerid,t.specid,t.resourceid,t.teamid,t.qty as DispatchQty,t.reportedqty,t.PlannedCompletionDate,c.qty as OriginalQty,s1.specid as currentspecid1," & vbCrLf
        strQuery += "sb1.specname as currentspecname1 ,sb2.specname as currentspecname2,sb2.revofrcdid as currentspecid2,f1.factoryname as specFactory from workcenterdispatching t " & vbCrLf
        strQuery += "left join spec s on s.specid=t.specid " & vbCrLf
        strQuery += "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf
        strQuery += "left join container c on c.containerid=t.containerid " & vbCrLf
        strQuery += "left join resourcedef r on r.resourceid=t.resourceid " & vbCrLf
        strQuery += "left join team te on te.teamid=t.teamid " & vbCrLf
        strQuery += "left join factory f on f.factoryid=c.originalfactoryid " & vbCrLf
        strQuery += "left join factory f1 on f1.factoryid=s.factoryid" & vbCrLf
        strQuery += "left join product p  on p.productid=c.productid " & vbCrLf
        strQuery += "left join mfgorder m on m.mfgorderid=c.mfgorderid " & vbCrLf
        strQuery += "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf
        strQuery += "left join currentstatus cs on cs.currentstatusid=c.currentstatusid " & vbCrLf
        strQuery += "left join workflowstep wt on wt.workflowstepid=cs.workflowstepid " & vbCrLf
        strQuery += "left join spec s1 on s1.specid=wt.specid " & vbCrLf
        strQuery += "left join specbase sb1 on sb1.specbaseid=s1.specbaseid " & vbCrLf
        strQuery += "left join specbase sb2 on sb2.specbaseid=wt.specbaseid " & vbCrLf
        strQuery += " where (t.status=1 or t.status=0 or t.status=3 ) and c.status=1  and c.containerid<>c.containername and c.parentcontainerid is null "


        If para.Keys.Contains("ContainerName") = True Then
            If para("ContainerName").ToString().Trim() <> "" Then
                strQuery += "AND LOWER(c.containername) like '%" & para("ContainerName").ToString.ToLower & "%'" & vbCrLf
            End If
        End If

        If para.Keys.Contains("factoryName") = True Then
            If para("factoryName").ToString().Trim() <> "" Then
                strQuery += "AND LOWER(f.factoryname)= '" & para("factoryName").ToString.ToLower & "'" & vbCrLf
            End If
        End If

        If para.Keys.Contains("productname") = True Then
            If para("productname").ToString().Trim() <> "" Then
                strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("productname").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("productname").ToString.ToLower & "%')" & vbCrLf
            End If
        End If

        If para.Keys.Contains("processno") = True Then
            If para("processno").ToString().Trim() <> "" Then
                strQuery += "AND LOWER(p.processno) LIKE '%" & para("processno").ToString.ToLower & "%'" & vbCrLf
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult
        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
            Dim DT As DataTable = r.DBTable.Copy

            For Each row As DataRow In DT.Rows
                If row.IsNull("currentspecname2") = False Then
                    row("currentspecname1") = row("currentspecname2")
                    row("currentspecid1") = row("currentspecid2")
                End If
            Next

            'DT = DataAdjust.FormatDataContainerNameByColumNameBJ("containername", DT)
            'DT = DataAdjust.FormatDataSetSpecNameByColumNameBJ("specName", DT)

            result.Result = True
            result.DBTable = DT
            result.RowCount = r.RowCount
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
#End Region

#Region "BJ获取可以修改的派工单信息"
    Public Function BJ_DispatchInfoByEdit(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.BJ_DispatchInfoByEdit
        Dim strQuery As String = "select m.mfgordername, p.productid,p.productrevision,pb.productname,sb.specname,s.specrevision,p.description," & vbCrLf
        strQuery += "c.containername, r.resourceename, r.ResourceName, te.teamname, f.factoryname,f.description as mfactoryName , " & vbCrLf
        strQuery += "DECODE(t.status,'0','已派工','1','已接收','2','已报工','3','已完成','4','已关闭') AS status,t.workcenterdispatchingid,t.workcenterdispatchingname," & vbCrLf
        strQuery += "(CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold," & vbCrLf
        strQuery += "t.containerid,t.specid,t.resourceid,t.teamid,t.qty as DispatchQty,t.reportedqty,t.PlannedCompletionDate,c.qty as OriginalQty,e.fullname " & vbCrLf
        strQuery += " from workcenterdispatching t " & vbCrLf
        strQuery += "left join spec s on s.specid=t.specid " & vbCrLf
        strQuery += "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf
        strQuery += "LEFT JOIN employee e ON e.employeeid = t.mainmfgemployeeid " & vbCrLf
        strQuery += "left join container c on c.containerid=t.containerid " & vbCrLf
        strQuery += "left join resourcedef r on r.resourceid=t.resourceid " & vbCrLf
        strQuery += "left join team te on te.teamid=t.teamid " & vbCrLf
        strQuery += "left join factory f on f.factoryid=c.originalfactoryid " & vbCrLf
        strQuery += "left join product p  on p.productid=c.productid " & vbCrLf
        strQuery += "left join mfgorder m on m.mfgorderid=c.mfgorderid " & vbCrLf
        strQuery += "left join productbase pb on pb.productbaseid=p.productbaseid  " & vbCrLf
        strQuery += "where (t.status=1 or t.status=0 or t.status=2 ) and c.status=1  and c.containerid<>c.containername and c.parentcontainerid is null "

        If para.Keys.Contains("ContainerName") = True Then
            If para("ContainerName").ToString().Trim() <> "" Then
                strQuery += "AND LOWER(c.containername) like '%" & para("ContainerName").ToString.ToLower & "%'" & vbCrLf
            End If
        End If

        If para.Keys.Contains("factoryName") = True Then
            If para("factoryName").ToString().Trim() <> "" Then
                strQuery += "AND LOWER(f.factoryname)= '" & para("factoryName").ToString.ToLower & "'" & vbCrLf
            End If
        End If

        If para.Keys.Contains("ProductName") = True Then
            If para("ProductName").ToString().Trim() <> "" Then
                strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
            End If
        End If

        If para.Keys.Contains("processno") = True Then
            If para("processno").ToString().Trim() <> "" Then
                strQuery += "AND LOWER(p.processno) LIKE '%" & para("processno").ToString.ToLower & "%' " & vbCrLf
            End If
        End If

        'If para.Keys.Contains("specName") = True Then
        '    If para("specName").ToString().Trim() <> "" Then
        '        strQuery += " AND LOWER(sb.specname)= '" & para("specName").ToString.ToLower & "'" & vbCrLf
        '    End If
        'End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
        Dim DT As DataTable = r.DBTable.Copy

        'DT = DataAdjust.FormatDataContainerNameByColumNameBJ("containername", DT)
        'DT = DataAdjust.FormatDataSetSpecNameByColumNameBJ("specName", DT)

        result.Result = True
        result.DBTable = DT
        result.RowCount = r.RowCount

        Return result
    End Function
#End Region

#Region "根据批次工序查询派工单"
    Public Function GetWorkDispacthInfo(ByVal strContainerName As String, ByVal strSpecName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetWorkDispacthInfo
        Dim oResult As New uMESProcessResult
        Try
            Dim strSql As String = "select w.containerid,w.specid,c.containername,sb.specname,w.qty,w.reportedqty,w.workcenterdispatchingname from workcenterdispatching w " & vbCrLf
            strSql += "left join container c on c.containerid=w.containerid " & vbCrLf
            strSql += "left join spec s on s.specid=w.specid " & vbCrLf
            strSql += "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf
            strSql += "where c.containername='" & strContainerName & "' and sb.specname='" & strSpecName & "'"

            Dim dispatchingTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            oResult.Result = True
            oResult.DBTable = dispatchingTable
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function
#End Region

#Region "获取跟踪卡的零件编号"
    Public Function GetProductNoForMark_bj(ByVal strContainerName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetProductNoForMark_bj
        Dim strQuery As String = "SELECT t.qualityrecordname,p.productno, " & vbCrLf & _
                                "(CASE WHEN c.status = 0 THEN '已删除' WHEN c.status = 1 THEN '正常'  WHEN c.status = 2 THEN '已关闭'  WHEN c.status = 3 THEN 'Shipped'  WHEN c.status = 4 THEN '已消耗' END) AS status" & vbCrLf & _
                                "FROM qualityrecord T " & vbCrLf & _
                                "INNER JOIN container c on c.containerid= t.containerid" & vbCrLf & _
                                "LEFT JOIN ProductNoInfo p on p.qualityrecordid = t.qualityrecordid" & vbCrLf & _
                                "WHERE T.qualityrecordname in (" & strContainerName & ")" & vbCrLf & _
                                "ORDER by p.productno ASC " & vbCrLf

        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = dal.GetProductNoForMark(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
#End Region

#Region "获取跟踪卡未进入质量记载的零件号"
    Public Function GetProductNoForQuaility_bj(ByVal strContainerName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetProductNoForQuaility_bj
        Dim strQuery As String = "select cc.containername, c.containerid,ck.partno ,ck.checkresulttype  from container c " & vbCrLf & _
                                 "inner join container cc on cc.containerid=c.parentcontainerid " & vbCrLf & _
                                 "inner join CheckPartNOList ck on c.containername=ck.partno " & vbCrLf & _
                                 "where ck.partno not in (select p.productno from ProductNoInfo p) and ck.checkresulttype=2 and cc.containername='" & strContainerName & "'"

        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = DatabaseHelper.Query_ReturnDatTable(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
#End Region


#Region "得到问题零件号，没有进入质量记载单"
    Public Function GetChildContainerNoQuaility(ByVal strContainerName As String, ByVal strSpecName As String) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetChildContainerNoQuaility

        Dim strSql As String = "SELECT t.Conventioncheckhistoryid, t.containerid,t.containername,t.specid,p.PartNO, p.Checkresulttype,sb.specname FROM ConventionCheckhistory t " & vbCrLf & _
                               "INNER JOIN ConventionCheckHistoryCheckPar tp ON t.ConventionCheckhistoryid = tp.ConventionCheckhistoryid " & vbCrLf & _
                               "INNER JOIN CheckPartNOList p ON tp.Checkpartnolistid = p.Checkpartnolistid " & vbCrLf & _
                               "LEFT JOIN spec s on s.specid=t.specid " & vbCrLf & _
                               "LEFT JOIN specbase sb on sb.specbaseid=s.specbaseid  where p.partno not in (select pt.productno from ProductNoInfo pt) " & vbCrLf & _
                               "AND p.checkresulttype=2 and  t.containername = '" & strContainerName & "' AND sb.Specname = '" & strSpecName & "'"
        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result

    End Function


#End Region

#Region "获取生产订单列表"
    Function GetMfgOrderList_bj(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetMfgOrderList_bj
        Dim strQuery = "select m.mfgordername,m.reportingfactoryid,m.qty,pb.productname,p.processno,p.productrevision,p.productid,pb1.productname as productname1,pb1.revofrcdid," & vbCrLf &
                       "p1.processno as processno1,p1.productrevision as productrevsion1,p.description,p1.description as productDesc," & vbCrLf &
                       "f.factoryname,f.description as factoryDesc,p.isnumber,p1.isnumber as isnumber1,m.plannedstartdate,m.plannedcompletiondate," & vbCrLf &
                       "mi.realqty1,mi.realqty2,nvl(a1.qty1,0) as qty1 ,mi.Bjmaterialinfoname from mfgorder m " & vbCrLf &
                       "left join product p  on p.productid=m.productid " & vbCrLf &
                       "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf &
                       "left join productbase pb1 on pb1.productbaseid=m.productbaseid " & vbCrLf &
                       "left join product p1 on p1.productid=pb1.revofrcdid " & vbCrLf &
                       "left join factory f on f.factoryid=m.reportingfactoryid " & vbCrLf &
                       "left join bjMaterialInfo mi on mi.mfgorderid=m.mfgorderid " & vbCrLf &
                       "Left Join " & vbCrLf &
                       "(select t.mfgorderid,sum(t.qty) as qty1 from container t where t.containerid<>t.containername and t.mfgorderid is not null  and t.parentcontainerid is null group by t.mfgorderid) a1 " & vbCrLf &
                       "on a1.mfgorderid=m.mfgorderid where mi.realqty1<>0  and nvl(a1.qty1,0)< mi.realqty1 "

        If para.Keys.Contains("FactoryName") = True Then
            strQuery += " AND LOWER(f.factoryname) = '" & para("FactoryName").ToString.ToLower & "' " & vbCrLf
        End If

        If para.Keys.Contains("MfgOrderName") = True Then
            strQuery += " AND LOWER(m.mfgordername) LIKE '%" & para("MfgOrderName").ToString.ToLower & "%' " & vbCrLf
        End If

        If para.Keys.Contains("ProductName") = True Then
            strQuery += " AND(LOWER(pb1.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' or LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If

        If para.Keys.Contains("ProductDesc") = True Then
            strQuery += " AND(LOWER(p1.description) LIKE '%" & para("ProductDesc").ToString.ToLower & "%' or LOWER(p.description) LIKE '%" & para("ProductDesc").ToString.ToLower & "%')" & vbCrLf
        End If

        If para.Keys.Contains("ProcessNO") = True Then
            strQuery += " AND(LOWER(p1.processno) LIKE '%" & para("ProcessNO").ToString.ToLower & "%' or LOWER(p.processno) LIKE '%" & para("ProcessNO").ToString.ToLower & "%')" & vbCrLf
        End If
        strQuery += " order by m.mfgordername desc"


        'strQuery = "select " & vbCrLf &
        '    "a1.mfgorderid,a1.mfgordername,a1.productname,a1.processno,a1.productid,a1.description,a1.productrevision,a1.isnumber,a1.uomname,a1.workflowname,a1.workflowrevision, " & vbCrLf &
        '    "a1.qty,a1.plannedstartdate,a1.plannedcompletiondate,a1.orderstatusid,a1.factoryname,a1.prioritycodename,a1.orderstatusname," & vbCrLf &
        '    "nvl(a2.qty1,0)qty1,(a1.qty - nvl(a2.qty1,0))qty2,Bjmaterialinfoname,facDisc from" & vbCrLf &
        '    "(select t.mfgorderid,t.mfgordername,nvl(pb1.productname,pb2.productname)productname,nvl(p1.processno,p2.processno)processno,nvl(p1.productid,p2.productid)productid, " & vbCrLf &
        '    "nvl(p1.description,p2.description)description,nvl(p1.productrevision,p2.productrevision)productrevision, " & vbCrLf &
        '    "nvl(p1.isnumber,p2.isnumber)isnumber,u.uomname, " & vbCrLf &
        '    "Coalesce(wb1.workflowname,wb2.workflowname,wb3.workflowname,wb4.workflowname)workflowname," & vbCrLf &
        '    "Coalesce(w1.workflowrevision,w2.workflowrevision,w3.workflowrevision,w4.workflowrevision)workflowrevision," & vbCrLf &
        '    "t.qty,mstock.qty1,t.plannedstartdate,t.plannedcompletiondate,t.orderstatusid,f.factoryname,pc.prioritycodename,os.orderstatusname,bjM.Bjmaterialinfoname,f.description facDisc from mfgorder t " & vbCrLf &
        '    "left join product p1 on p1.productid = t.productid " & vbCrLf &
        '    "left join productbase pb1 on pb1.productbaseid = p1.productbaseid " & vbCrLf &
        '    "left join productbase pb2 on pb2.productbaseid = t.productbaseid " & vbCrLf &
        '    "left join product p2 on p2.productid = pb2.revofrcdid " & vbCrLf &
        '    "left join workflow w1 on w1.workflowid = p1.workflowid " & vbCrLf &
        '    "left join workflowbase wb1 on wb1.workflowbaseid=w1.workflowbaseid" & vbCrLf &
        '    "left join workflowbase wb2 on wb2.workflowbaseid = p1.workflowbaseid" & vbCrLf &
        '    "left join workflow w2 on w2.workflowbaseid = wb2.workflowbaseid" & vbCrLf &
        '    "left join workflow w3 on  w3.workflowid=p2.workflowid" & vbCrLf &
        '    "left join workflowbase wb3 on wb3.workflowbaseid=w3.workflowbaseid" & vbCrLf &
        '    "left join workflowbase wb4 on wb4.workflowbaseid = p2.workflowbaseid" & vbCrLf &
        '    "left join workflow w4 on w4.workflowbaseid = wb4.workflowbaseid" & vbCrLf &
        '    "left join factory f on f.factoryid = t.reportingfactoryid " & vbCrLf &
        '    "left join PriorityCode pc on pc.prioritycodeid= t.priorityid" & vbCrLf &
        '    "left join uom u on u.uomid=t.uomid" & vbCrLf &
        '    "left join bjMaterialInfo mi on mi.mfgorderid = t.mfgorderid " & vbCrLf &
        '    "left join orderstatus os on os.orderstatusid = t.orderstatusid " & vbCrLf &
        '    "left join MaterialStock mstock on mstock.mfgorderid = t.mfgorderid " & vbCrLf &
        '    "left join bjmaterialinfo bjM on bjM.Mfgorderid = t.mfgorderid " & vbCrLf &
        '    "where 1=1 "



        'If para.Keys.Contains("MaterialState") = True Then
        '    If para("MaterialState").ToString = "未入库" Then
        '        strQuery += " AND bjM.Bjmaterialinfoname is null " & vbCrLf
        '    ElseIf para("MaterialState").ToString = "已入库" Then
        '        strQuery += " AND bjM.Bjmaterialinfoname is not null " & vbCrLf
        '    End If

        'End If

        'strQuery += ")a1 left join (select t.mfgorderid,sum(t.qty)qty1 from container t where t.containertype<>4 and t.mfgorderid is not null group by t.mfgorderid) a2 on a2.mfgorderid = a1.mfgorderid"
        'strQuery += " where a1.qty - nvl(a2.qty1,0)>0"
        'strQuery += " order by a1.mfgordername desc"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
            Dim DT As DataTable = r.DBTable

            DT.Columns.Add("planBeginDate")
            DT.Columns.Add("planEndDate")
            DT.Columns.Add("planQty") '可投数量
            DT.Columns.Add("isnumberName") '是否编号


            For Each row As DataRow In DT.Rows
                'm.plannedstartdate,m.plannedcompletiondate

                If row.IsNull("productname1") = False And row("productname1").ToString().Trim() <> "" Then
                    row("productname") = row("productname1")
                End If
                If row.IsNull("revofrcdid") = False And row("revofrcdid").ToString().Trim() <> "" Then
                    row("productid") = row("revofrcdid")
                End If
                If row.IsNull("processno1") = False And row("processno1").ToString().Trim() <> "" Then
                    row("processno") = row("processno1")
                End If
                If row.IsNull("productrevsion1") = False And row("productrevsion1").ToString().Trim() <> "" Then
                    row("productrevision") = row("productrevsion1")
                End If
                If row.IsNull("productDesc") = False And row("productDesc").ToString().Trim() <> "" Then
                    row("description") = row("productDesc")
                End If
                If row.IsNull("isnumber1") = False Then
                    row("isnumber") = row("isnumber1")
                End If

                If row.IsNull("isnumber") = False Then
                    If Convert.ToInt32(row("isnumber")) = 0 Then
                        row("isnumberName") = "否"
                    Else
                        row("isnumberName") = "是"
                    End If
                Else
                    row("isnumberName") = "否"
                End If

                Dim tmpDate As DateTime = Convert.ToDateTime(row("plannedcompletiondate"))
                row("planEndDate") = tmpDate.ToShortDateString()
                tmpDate = Convert.ToDateTime(row("plannedstartdate"))
                row("planBeginDate") = tmpDate.ToShortDateString()
                If row.IsNull("qty1") = False Then
                    row("planQty") = Convert.ToInt32(row("realqty1")) - Convert.ToInt32(row("qty1"))
                End If
            Next

            result.Result = True
            result.DBTable = DT
            result.RowCount = r.RowCount
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    Function GetMfgOrderList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjDispatchIBusiness.GetMfgOrderList
        Dim strQuery As String = "select t.*,p.productrevision,w.workflowrevision ,wb.workflowname,wb1.workflowname as workflowname1 ,w1.workflowrevision as workflowrevisoin1 from MfgPlanForMonth t " & vbCrLf &
                                 "left join productbase pb on pb.productname=t.productname " & vbCrLf &
                                 "left join product p on p.productid=pb.revofrcdid " & vbCrLf &
                                 "left join workflow w on w.workflowid=p.workflowid " & vbCrLf &
                                 "left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid " & vbCrLf &
                                 "left join workflowbase wb1 on wb1.workflowbaseid=p.workflowbaseid " & vbCrLf &
                                 "left join workflow w1 on w1.workflowid=wb1.revofrcdid " & vbCrLf

        If para.Keys.Contains("ProductName") = True Then
            strQuery += " AND LOWER(t.ProductName) LIKE '%" & para("ProductName").ToString.ToLower & "%' " & vbCrLf
        End If

        If para.Keys.Contains("planMonth") = True Then
            strQuery += " AND PlanMonth='" & para("planMonth").ToString().Trim() & "'" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
            Dim DT As DataTable = r.DBTable
            result.Result = True
            result.DBTable = DT
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
#End Region

#Region "得到工艺路线完整信息"
    Public Function GetWorkFlowInfo(ByVal workFlowName As String, ByVal workFlowVersion As String) As DataTable Implements uMES_bjDispatchIBusiness.GetWorkFlowInfo

        Dim strQuery As String = "select wt.workflowstepid,wt.workflowstepname from workflowstep wt " & vbCrLf & _
                                 "left join workflow w on w.workflowid=wt.workflowid " & vbCrLf & _
                                 "left join workflowbase wb  on wb.workflowbaseid=w.workflowbaseid " & vbCrLf & _
                                 "where wb.workflowname='" & workFlowName & "' and   w.workflowrevision='" & workFlowVersion & "' order by wt.sequence"
        Dim tmpDT As DataTable = DatabaseHelper.Query_ReturnDatTable(strQuery)
        Return tmpDT
    End Function
#End Region


End Class
